SQL - Combine multiple rows into single row with dynamic number of columns

Question

I'm trying to setup a query that selects multiple rows from a table and combines similar rows into a single row with multiple columns. I believe I can do this with pivot however each row won't have the same number of columns and that's where I'm running into problems. I gave an example below of what I mean.

This:

Account    Period    Amount
01         0001      1111
01         0002      2222
01         0003      3333
02         0001      1111
03         0001      1111
04         0001      1111
04         0002      2222

Should be come this:

Account    0001    0002   0003   
01         1111    2222   3333
02         1111      
03         1111
04         1111    2222

Here is my initial query that's pulling all the data together:

    WITH CTE AS(

    SELECT
      a.Period, a.Account, SUM(a.Amount) Amount
    FROM
      LedgerAP a
    WHERE
      a.Period >= 201500
    GROUP BY a.Period, a.Account

    UNION

    SELECT 
      b.Period, b.Account, SUM(b.Amount) Amount
    FROM
      LedgerAR b
    WHERE
      b.Period >= 201500
    GROUP BY b.Period, b.Account

    UNION

    SELECT
      c.Period, c.Account, SUM(c.Amount)
    FROM
      LedgerEx c
    WHERE
      c.Period >= 201500
    GROUP BY c.Period, c.Account

    UNION

    SELECT
      d.Period, d.Account, SUM(d.Amount)
    FROM
      LedgerMisc d
    WHERE
      d.Period >= 201500
    GROUP BY d.Period, d.Account

    )

    SELECT account,
           max(case when period = @Budgetyear + '01' then SUM(amount) end) Amount1,
           max(case when period = @Budgetyear + '02' then SUM(amount) end) Amount2,
           max(case when period = @Budgetyear + '03' then SUM(amount) end) Amount3,
           max(case when period = @Budgetyear + '04' then SUM(amount) end) Amount4,
           max(case when period = @Budgetyear + '05' then SUM(amount) end) Amount5,
           max(case when period = @Budgetyear + '06' then SUM(amount) end) Amount6,
           max(case when period = @Budgetyear + '07' then SUM(amount) end) Amount7,
           max(case when period = @Budgetyear + '08' then SUM(amount) end) Amount8,
           max(case when period = @Budgetyear + '09' then SUM(amount) end) Amount9,
           max(case when period = @Budgetyear + '10' then SUM(amount) end) Amount10,
           max(case when period = @Budgetyear + '11' then SUM(amount) end) Amount11,
           max(case when period = @Budgetyear + '12' then SUM(amount) end) Amount12

FROM CTE
GROUP BY account
ORDER BY account ASC

Now how can I go about organizing this like I have shown above? Any help would be amazing!

I edited the post to show the current way I'm trying to do it, but I have an aggregate inside an aggregate so its not liking that — user41829, Nov 11 '15 at 17:29

score 0 · Answer 1 · edited May 23 '17 at 11:44

Credit to @Bluefeet's solution here, you can build up dynamic pivot something like this:

create table table1 (Account varchar(2), Period varchar(4), Amount int)

insert into table1 values
('01', '0001', 1111),
('01', '0002', 2222),
('01', '0003', 3333),
('02', '0001', 1111),
('03', '0001', 1111),
('04', '0001', 1111),
('04', '0002', 2222);

Dynamic Query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.period) 
            FROM table1 t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Account, ' + @cols + ' from 
            (
                select Account
                    , Period
                    , Amount
                from table1
           ) x
            pivot 
            (
                 max(amount)
                for period in (' + @cols + ')
            ) p '


execute(@query)
GO

Result:

+---------+------+--------+--------+
| Account | 0001 |  0002  |  0003  |
+---------+------+--------+--------+
|      01 | 1111 | 2222   | 3333   |
|      02 | 1111 | (null) | (null) |
|      03 | 1111 | (null) | (null) |
|      04 | 1111 | 2222   | (null) |
+---------+------+--------+--------+

SQL Fiddle Demo

score -1 · Answer 2 · answered Nov 11 '15 at 17:19

Your basic pivot:

SELECT
SUM(case Period when '0001' then Amount end) as '0001',
SUM(case Period when '0002' then Amount end) as '0002',
SUM(case Period when '0003' then Amount end) as '0003'
FROM LedgerAP
GROUP BY Account

To create that query dynamically, which is helpful if you have many values for Period:

DECLARE @SQL varchar(max) = 'SELECT '
;WITH Periods AS
(
SELECT DISTINCT Period
FROM LedgerAP
)
SELECT @SQL = @SQL + 
    'SUM(case Period when ''' + Period + ''' then Amount end) as ''' + Period + ''','

SET @SQL = LEFT(@SQL,LEN(@SQL) - 1) + ' FROM LedgerAP GROUP BY Account'
EXEC(@SQL)

SQL - Combine multiple rows into single row with dynamic number of columns

2 Answers2