how write a sql query for pivot table

Posted by Cpatil1000 under ASP.NET on 8/19/2013 | Points: 10 | Views : 10762 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

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

Latest Posts