SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pivot on two columns


Pivot on two columns

Author
Message
SQLCereal
SQLCereal
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 759
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59148 Visits: 17947
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 Modens 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)
SQLCereal
SQLCereal
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 759
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59148 Visits: 17947
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 Modens 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)
SQLCereal
SQLCereal
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 759
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59148 Visits: 17947
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 Modens 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)
SQLCereal
SQLCereal
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 759
Hmmm...I'll have to play around with those. Thanks!

-Jeremy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search