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