I expecting result like this urgently please help me

Posted by Johnseelan under Sql Server on 10/19/2012 | Points: 10 | Views : 1168 | Status : [Member] | Replies : 2
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




Responses

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

Up
0
Down
Please try the following query that using PIVOT

SELECT checked
,max([A]) [A]
, max()
,max([C])[C]

FROM #TEST

PIVOT

( sum (RESPONSES )FOR SEGMENT IN ([A],,[C])

) AS p group by checked

Please use the attached query ,because there is some problems in the displayed...

Please mark as answer if it helps...
 Download source file

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

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

Up
0
Down
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

Login to post response