how write a sql query for pivot table

Posted by Cpatil1000 under ASP.NET on 8/19/2013 | Points: 10 | Views : 11414 | Status : [Member] | Replies : 5
hi, need a write sql query with pivot table for following table


his is Table data…
ID DATE RAW_SOURCE RAW WATER PURE_SOURCE PURE WATER OUTGOING WATER MBR OUTGOING WATER ESR
47 1/8/2013 Sakhare Dam 5.94 Sakhare Dam 5.84 5.84 5.84
46 2/8/2013 Sakhare Dam 3.75 Sakhare Dam 3.65 3.65 3.65
45 3/8/2013 Sakhare Dam 5.3 Sakhare Dam 5.2 5.2 5.2
44 4/8/2013 Sakhare Dam 4.75 Sakhare Dam 4.65 4.65 4.65
43 5/8/2013 Sakhare Dam 4.84 Sakhare Dam 4.74 4.74 4.74
42 6/8/2013 Sakhare Dam 5.85 Sakhare Dam 5.75 5.75 5.75
41 7/8/2013 Sakhare Dam 6.21 Sakhare Dam 6.11 6.11 6.11
30 8/8/2013 Sakhare Dam 6.12 Sakhare Dam 6.02 6.02 6.02
31 9/8/2013 Sakhare Dam 4.48 Sakhare Dam 4.38 4.38 4.38
32 10/8/2013 Sakhare Dam 6.85 Sakhare Dam 6.75 6.75 6.75
33 11/8/2013 Sakhare Dam 6.67 Sakhare Dam 6.57 6.57 6.57
34 12/8/2013 Sakhare Dam 6.4 Sakhare Dam 6.3 6.3 6.3
35 13-08-2013 Sakhare Dam 6.03 Sakhare Dam 5.93 5.93 5.93
36 14-08-2013 Sakhare Dam 6.12 Sakhare Dam 6.02 6.02 6.02

I want output..

Date Sakhare Dam Sakhare Dam OUTGOING WATER MBR OUTGOING WATER ESR
1/8/2013 5.94 5.84 5.84 5.84




Responses

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

Up
0
Down
Do you have only one type of RAW_SOURCE ? If Yes
SELECT DATE, CASE WHEN RAW_SOURCE = 'Sakhare Dam' THEN [RAW WATER] END 'Sakhare Dam'

,CASE WHEN [PURE_SOURCE] = 'Sakhare Dam' THEN [PURE WATER] END 'Sakhare Dam'
,[OUTGOING WATER MBR]
,[OUTGOING WATER ESR]
FROM TableName


If NOT the above, for more than one RAW_SOURCE/PURE_SOURCE do you want to extend the number of columns?

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

Posted by: Ssj_Kumar on: 8/19/2013 [Member] Starter | Points: 25

Up
0
Down
Please find the below link which helps you to create pivot query
use 3rd example for your required, If your columns or dynamic you need to go for dynamic query
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Regards,
Jayakumar Selvakani

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

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

Up
0
Down
Hi Chandrashekhar,
We can use dynamic pivot/static pivot based on the scenarios... tell us the exact requirement.. For the above posted sample data there is no need of pivoting....

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

Posted by: Ssj_Kumar on: 8/20/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Bandi,
Can you explain me, how without pivot you can bring value as a column name. don't go for hard coding that is not a correct solution.
In his case "Sakhare Dam" is a value not a column, please explain me if I am wrong

Regards,
Jayakumar Selvakani

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

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

Up
0
Down
If there is only one RAW_Source and one pure_source we can use Dynamic SQL query( not the dynamic pivot)
CREATE TABLE TableName(ID int, DATE DATE, [RAW_SOURCE] varchar(30), [RAW WATER]  DEC(5,2), [PURE_SOURCE] varchar(30), [PURE WATER] dec(5,2), [OUTGOING WATER MBR] dec(5,2), [OUTGOING WATER ESR] dec(5,2))

insert into TableName
SELECT 47 , '1/8/2013', 'Sakhare Dam', 5.94, 'Sakhare Dam', 5.84,5.84, 5.84 union all
SELECT 46 , '2/8/2013', 'Sakhare Dam', 3.75 , 'Sakhare Dam',3.65 ,3.65, 3.65 union all
SELECT 45 , '3/8/2013', 'Sakhare Dam',5.3 , 'Sakhare Dam',5.2 ,5.2 ,5.2 union all
SELECT 44 , '4/8/2013', 'Sakhare Dam', 4.75 , 'Sakhare Dam', 4.65 ,4.65, 4.65 union all
SELECT 43 , '5/8/2013', 'Sakhare Dam', 4.84 , 'Sakhare Dam',4.74 ,4.74, 4.74 union all
SELECT 42 , '6/8/2013', 'Sakhare Dam', 5.85 , 'Sakhare Dam',5.75 ,5.75 ,5.75 union all
SELECT 41 , '7/8/2013', 'Sakhare Dam', 6.21 , 'Sakhare Dam',6.11 ,6.11 ,6.11 union all
SELECT 30 , '8/8/2013', 'Sakhare Dam', 6.12 , 'Sakhare Dam', 6.02, 6.02, 6.02 union all
SELECT 31 , '9/8/2013', 'Sakhare Dam', 4.48 , 'Sakhare Dam',4.38 ,4.38 ,4.38 union all
SELECT 32 , '10/8/2013', 'Sakhare Dam', 6.85 , 'Sakhare Dam',6.75 ,6.75, 6.75 union all
SELECT 33 , '11/8/2013', 'Sakhare Dam', 6.67 , 'Sakhare Dam', 6.57, 6.57, 6.57 union all
SELECT 34 , '12/8/2013', 'Sakhare Dam', 6.4 , 'Sakhare Dam', 6.3 ,6.3, 6.3 union all
SELECT 35 , '13-08-2013', 'Sakhare Dam', 6.03 , 'Sakhare Dam', 5.93, 5.93, 5.93 union all
SELECT 36, '14-08-2013', 'Sakhare Dam', 6.12 , 'Sakhare Dam', 6.02, 6.02, 6.02

DECLARE @Raw_ColValue VARCHAR(40)='', @Pure_ColValue VARCHAR(40) = '';
SELECT @Raw_ColValue = [RAW_SOURCE], @Pure_ColValue = [PURE_SOURCE] FROM TableName
DECLARE @SQL VARCHAR(MAX) = ''
SET @SQL = 'SELECT DATE, CASE WHEN RAW_SOURCE = '''+@Raw_ColValue+''' THEN [RAW WATER] END AS ''' + @Raw_ColValue +'''
,CASE WHEN [PURE_SOURCE] = '''+ @Pure_ColValue + ''' THEN [PURE WATER] END AS ''' + @Pure_ColValue +'''
,[OUTGOING WATER MBR]
,[OUTGOING WATER ESR]
FROM TableName;'
EXEC( @SQL)

DROP TABLE TableName


NOTE: Assume that they wish to display data for only one RAW_SOURCE and one Pure_SOURCE...
If there are multiple number of raw/pure sources we have to go ahead with Dynamic PIVOT....

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