How to pivote data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to pivote data

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-06-11 : 07:20:32

Hi,

I have output of CTE as below
DATE AMOUNT SIGN
Jan 06 2012 448.32 I
Jan 06 2012 870.00 B
Jan 09 2012 576.96 W
Jan 12 2012 523.12 W
Jan 13 2012 1446.75 W
Jan 13 2012 870.00 B
Jan 13 2012 528.75 A
Jan 16 2012 185.62 W
Jan 16 2012 870.00 W
Jan 16 2012 319.20 B
Jan 16 2012 319.20 B
Jan 16 2012 870.00 W
Jan 16 2012 870.00 W
Jan 16 2012 870.00 W
Jan 16 2012 870.00 W

and I need to change it as :
DATE Total Amount I B W A
Jan 06 2012 1318.32 1 1 0 0
Jan 09 2012 576.96 0 0 1 0
Jan 12 2012 523.12 0 0 1 0
Jan 13 2012 2845.50 0 1 1 1
Jan 16 2012 5174.02 0 2 6 0

Can someone help me ASAP please?
Thanks!
Pushp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-11 : 07:59:54

If you have only the signs you have listed in your sample, you can use the following:

SELECT
	DATE,
	SUM(amount),
	SUM(CASE WHEN SIGN = 'I' THEN 1 ELSE 0 END) AS [I],
	SUM(CASE WHEN SIGN = 'B' THEN 1 ELSE 0 END) AS [B],
	SUM(CASE WHEN SIGN = 'W' THEN 1 ELSE 0 END) AS [W],
	SUM(CASE WHEN SIGN = 'A' THEN 1 ELSE 0 END) AS [A]
FROM
	CTE
GROUP BY DATE
ORDER BY DATE

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-11 : 08:17:50

Here are a few variations. What you want can be achieved using Case Statements, Static Pivot and Dynamic Pivot. Dynamic Pivot would be my pick of doing it.


--Creating Table

Create Table Ex
(DATE varchar(30),
 AMOUNT	Float,
 SIGN Char(1) )


--Inserting Sample Data

Insert into Ex
Select 'Jan 06 2012', 448.32,	'I'
Union ALL
Select 'Jan 06 2012', 870.00,	'B'
Union ALL
Select 'Jan 09 2012', 576.96,	'W'
Union ALL
Select 'Jan 12 2012', 523.12,	'W'
Union ALL
Select 'Jan 13 2012', 1446.75,	'W'
Union ALL
Select 'Jan 13 2012', 870.00,	'B'
Union ALL
Select 'Jan 13 2012', 528.75,	'A'
Union ALL
Select 'Jan 16 2012', 185.62,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 319.20,	'B'
Union ALL
Select 'Jan 16 2012', 319.20,	'B'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'


--Query Using Case

Select DATE, SUM(Amount) As Amount,
Sum(Case When SIGN = 'I' Then 1 Else 0 End) As I,
Sum(Case When SIGN = 'B' Then 1 Else 0 End) As B,
Sum(Case When SIGN = 'W' Then 1 Else 0 End) As W,
Sum(Case When SIGN = 'A' Then 1 Else 0 End) As A
From Ex
Group By Date


--Static Pivot

Select a.DATE, Amount, [I], [B], [W], [A] From
(Select DATE, SUM(Amount) As Amount From Ex
Group By DATE) As a
JOIN
(Select Date, [I], [B], [W], [A] From Ex
 Pivot
 (Count(Amount) For Sign IN ([I], [B], [W], [A])) As pvt) As b on a.Date = b.Date


--Dynamic pivot

Declare @cols varchar(max), @sql varchar(max)
Declare @temp Table(Cols varchar(max) )
Insert Into @temp
Select Distinct SIGN From Ex
Select @cols = Coalesce(@cols + ', ', '') + Quotename(Cols) From @temp
Set @sql = 'Select a.DATE, Amount, '+@cols+' From
				(Select DATE, SUM(Amount) As Amount From Ex
				Group By DATE) As a
				JOIN
				(Select Date, '+@cols+' From Ex
				Pivot
				(Count(Amount) For Sign IN ('+@cols+')) As pvt) As b on a.Date = b.Date'
Execute (@sql)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:36:31

if you want to add the totals inline within same pivot query you can use below

http://visakhm.blogspot.com/2012/04/display-total-rows-with-pivotting-in-t.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-06-12 : 07:07:29

Thanks for the response guys... I applied the case functionality as it was easy to apply and was cost efficient as well against all other solutions.

Thanks
Pushkar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:10:29

quote:
Originally posted by pushp82

Thanks for the response guys... I applied the case functionality as it was easy to apply and was cost efficient as well against all other solutions.

Thanks
Pushkar

I hope you wont have new SIGN values added frequently in which case you might have to keep on modifying code each time to accomodate for the new values if using static solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources