I expecting result like this urgently please help me

Posted by Johnseelan under Sql Server on 10/19/2012 | Points: 10 | Views : 2059 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Definitions:

CREATE TABLE #TEST
(
ID INT,
SEGMENT CHAR (1),
RESPONSES INT,
CHECKED SMALLDATETIME
)

Sample Data:

INSERT INTO #TEST VALUES (1, 'A', 0, '2009-05-01')
INSERT INTO #TEST VALUES (2, 'B', 1, '2009-05-01')
INSERT INTO #TEST VALUES (3, 'C', 0, '2009-05-01')
INSERT INTO #TEST VALUES (4, 'A', 0, '2009-05-02')
INSERT INTO #TEST VALUES (5, 'B', 2, '2009-05-02')
INSERT INTO #TEST VALUES (6, 'C', 1, '2009-05-02')
INSERT INTO #TEST VALUES (7, 'A', 1, '2009-05-03')
INSERT INTO #TEST VALUES (8, 'B', 0, '2009-05-03')
INSERT INTO #TEST VALUES (9, 'C', 2, '2009-05-03')

Expected Result:

CHECKED A B C
2009-05-01 0 1 0
2009-05-02 0 2 1
2009-05-03 1 0 2

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 10/19/2012 [Member] [MVP] Silver | Points: 25

0	Check It Out! Select REPLACE(CONVERT(VARCHAR(10),Checked, 111),'/','-') Checked, "A","B","C" FROM ( Select Checked, Segment,Sum(Responses) Responses from Sample1 Group By Checked, Segment )AS P PIVOT ( SUM(Responses) FOR Segment IN ("A","B","C")) AS PVT ORDER BY 1 Result Checked A B C 2009-05-01 0 1 0 2009-05-02 0 2 1 2009-05-03 1 0 2 Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

Select REPLACE(CONVERT(VARCHAR(10),Checked, 111),'/','-') Checked, "A","B","C" FROM

(

Select Checked, Segment,Sum(Responses) Responses from Sample1

Group By Checked, Segment

)AS P

PIVOT

(

SUM(Responses)

FOR Segment IN ("A","B","C")) AS PVT

ORDER BY 1

Result

Checked	        A	B	C

2009-05-01	0	1	0

2009-05-02	0	2	1

2009-05-03	1	0	2

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts