Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot on two columns Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 2:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:12 PM
Points: 41, Visits: 541
I have a dataset similar to the following:

Account Date Amt

1111 20090228 200.00
1111 20090328 175.00
1111 20090428 250.00
1111 20090528 210.00
2222 20120115 100.00
2222 20120213 150.00
3333 20110605 300.00
3333 20110705 300.00
3333 20110805 300.00

I am trying to pivot on the date and the amount columns. The final output would look like this:

Account Date1 Amt1 Date2 Amt2 Date3 Amt3 Date4 Amt4

1111 20090228 200.00 20090328 175.00 20090428 250.00 20090528 210.00
2222 20120115 100.00 20120213 150.00
3333 20110605 300.00 20110705 300.00 20110805 300.00

I've attempted doing this using a CTE to add row numbers and then pivoting on the row numbers, which works well when just pivoting one column, but I have not been able to do this to pivot both columns out.

I think that it may be able to be done using two subsets or the original dataset and some sort of row numbering, but my mind is twisted in a knot thinking about it. Any help would be greatly appreciated.

-Jeremy




Post #1481568
Posted Tuesday, August 6, 2013 3:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
Please in the future post ddl and sample data in a consumable format. Look at this as an example.

if OBJECT_ID('tempdb..#Something') is not null
drop table #Something

create table #Something
(
Account int,
MyDate date,
Amt numeric(9,2)
)

set dateformat ymd

insert #Something
select 1111, '20090228', 200.00 union all
select 1111, '20090328', 175.00 union all
select 1111, '20090428', 250.00 union all
select 1111, '20090528', 210.00 union all
select 2222, '20120115', 100.00 union all
select 2222, '20120213', 150.00 union all
select 3333, '20110605', 300.00 union all
select 3333, '20110705', 300.00 union all
select 3333, '20110805', 300.00;

OK so no we have something to work with. I then have a question for you. Is there a defined max amount of columns in your data or does this need to be dynamic?

Since your data had a max of 4 sets per account I put together a query that will retrieve up to 4 groups.

with MyData as
(
select Account, MyDate, Amt, ROW_NUMBER() over (partition by Account order by MyDate) as RowNum
from #Something
)

select Account,
MAX(case when RowNum = 1 then MyDate end) as Date1,
MAX(case when RowNum = 1 then Amt end) as Amount1,
MAX(case when RowNum = 2 then MyDate end) as Date2,
MAX(case when RowNum = 2 then Amt end) as Amount2,
MAX(case when RowNum = 3 then MyDate end) as Date3,
MAX(case when RowNum = 3 then Amt end) as Amount3,
MAX(case when RowNum = 4 then MyDate end) as Date4,
MAX(case when RowNum = 4 then Amt end) as Amount4
from MyData
group by Account

If this needs to be dynamic we can do that too. I just didn't want to put forth the effort helping with that unless it is needed.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481580
Posted Tuesday, August 6, 2013 3:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:12 PM
Points: 41, Visits: 541
Thank you for the reply Sean. I will make sure to post the ddl and sample data in the future.

The example you've provided is interesting. I've never thought of doing it like that, but I do need this to be a dynamic pivot. I should have specified that in the original post. There may be up to 250 total columns.

-Jeremy
Post #1481585
Posted Tuesday, August 6, 2013 3:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
SQLCereal (8/6/2013)
Thank you for the reply Sean. I will make sure to post the ddl and sample data in the future.

The example you've provided is interesting. I've never thought of doing it like that, but I do need this to be a dynamic pivot. I should have specified that in the original post. There may be up to 250 total columns.

-Jeremy


I am tied up the rest of the day but you might take a look at the links in my signature about cross tabs.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481588
Posted Wednesday, August 7, 2013 1:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:12 PM
Points: 41, Visits: 541
After reading this article - http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-pivot-on-multiple-columns, I came up with the following solution:

SELECT [Account], [Date], [Amt]
, ROW_NUMBER() OVER (PARTITION BY DNUM ORDER BY EFTChkDate) AS rownum
INTO #pmts
FROM [Payments].[dbo].[PayData]
GO

DECLARE @sql nvarchar(MAX), @Cols nvarchar(max);

SELECT @Cols = (select ', ' + 'MAX(case when rownum = ' + CONVERT(varchar(20), rownum) + ' then CONVERT(VARCHAR(10), EFTChkDate, 101) else NULL end) AS ChkDate' + CONVERT(varchar(20), rownum) +
', MAX(case when rownum = ' + CONVERT(varchar(20), rownum) + ' then EFTChkAmt else NULL end) AS ChkAmt' + CONVERT(varchar(20), rownum)
from (select distinct rownum from #pmts) X ORDER BY rownum
FOR XML PATH(''))

SET @sql = 'SELECT DNUM' + @Cols + '
FROM #pmts
GROUP BY DNUM
ORDER BY DNUM'

EXECUTE(@sql)
GO

This worked perfectly for what I needed.

Sean - Would you be so kind as to let me know how you formatted your SQL code in the boxes like that?

-Jeremy
Post #1482042
Posted Wednesday, August 7, 2013 2:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
Glad you were able to figure it out.

You can use the IFCode shortcuts (over on the left) when posting to create code boxes.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1482048
Posted Wednesday, August 7, 2013 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:12 PM
Points: 41, Visits: 541
Hmmm...I'll have to play around with those. Thanks!

-Jeremy
Post #1482060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse