How write a pivot table in sql server

Posted by Cpatil1000 under Sql Server on 8/16/2013 | Points: 10 | Views : 1160 | Status : [Member] | Replies : 1
hi
I need a make pivot table in sql server or other how write a coding into asp.net. I want date wise output and make field SAKHARE DAM horizonal and date whould be vertical.. Kindly let me where is my sql queries wrong..

output..
RAW WATER SOURCE PURE WATER SOURCE
DATE SAKHARE DAM OUTGOING WATER MBR OUTGOING WATER ESR
01 0 0 0
02 0 0 0
03 0 0 0
04 0 0 0
05 0 0 0
06 0 0 0
07 0 0 0
08 6.02 6.02 6.02
09 4.38 4.38 4.38
10 6.75 6.75 6.75
11 6.57 6.57 6.57
12 6.30 6.30 6.30
13 5.93 5.93 5.93
14 6.02 6.02 6.02
15 6.02 6.02 6.02
16 0 0 0
17 0 0 0
18 0 0 0
19 0 0 0
20 0 0 0
21 0 0 0
22 0 0 0
23 0 0 0
24 0 0 0
25 0 0 0
26 0 0 0
27 0 0 0
28 0 0 0
29 0 0 0
30 0 0 0
31 0 0 0

Select [01],[02],[03].....[31], OUTGOING_WATER_MBR,OUTGOING WATER ESR,SOURCE_NAME,
From
(
Select WDN.DIVISION_ID,WATERWORK_ID, WDN.DAILY_NRW_ID As 'ID',Convert(varchar,WDN.DAILY_DATE, 105) 'DATE', DAY(WDN.DAILY_DATE) AS 'DAY',
(Select WS.SOURCE_NAME From WATERWORK_SOURCE WS Where WS.SOURCE_ID=WDR.SOURCE_ID) AS 'RAW_SOURCE',
(TOTAL_RAW_WATER_METER+TOTAL_RAW_WATER_UNMETER)As 'RAW WATER',
(Select WS.SOURCE_NAME From WATERWORK_SOURCE WS Where WS.SOURCE_ID=WDP.SOURCE_ID) 'PURE_SOURCE',
(TOTAL_PURE_WATER_METER+TOTAL_PURE_WATER_UNMETER)As 'PURE WATER'
,WDN.OUTGOING_WATER_MBR
,WDN.OUTGOING_WATER_ESR As 'OUTGOING WATER ESR'
From WATERWORK_DAILY_NRW WDN
Inner Join WATERWORK_DAILY_RAW_WATER WDR On WDR.DAILY_NRW_ID=WDN.DAILY_NRW_ID
Inner Join WATERWORK_DAILY_PURE_WATER WDP On WDP.DAILY_NRW_ID=WDN.DAILY_NRW_ID
Where WDN.DIVISION_ID=47
And WDN.WATERWORK_ID=57
And Month(WDN.DAILY_DATE)=8
And Year(WDN.DAILY_DATE)=2013) As P

PIVOT
(SUM(OUTGOING_WATER_MBR) FOR DAY IN ([08],[09],[10])) AS P




Responses

Posted by: Bandi on: 8/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
http://stackoverflow.com/questions/15260067/pivot-or-transpose-a-table-in-sql-server-without-grouping-by



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Login to post response