Pivot on two columns

  • 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

  • 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/

  • 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

  • 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/

  • 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

  • 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/

  • 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