Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 2855
Home > Forums > ASP.NET >

how write a sql query for pivot table

Posted by Cpatil1000 under ASP.NET on 8/19/2013 | Points: 10 | Views : 11429 | Status : [Member] | Replies : 5
Write New Post | Search Forums | AnsweredResolved 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

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

Latest Posts