August 6, 2013 at 2:49 pm
I have a dataset similar to the following:
AccountDate Amt
111120090228200.00
111120090328175.00
111120090428250.00
111120090528210.00
222220120115100.00
222220120213150.00
333320110605300.00
333320110705300.00
333320110805300.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
August 6, 2013 at 3:16 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 3:29 pm
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
August 6, 2013 at 3:39 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 1:50 pm
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
August 7, 2013 at 2:04 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 2:29 pm
Hmmm...I'll have to play around with those. Thanks!
-Jeremy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply