SQL Server Rows to Multi-Columns

Question

I have the following table and data:

CREATE TABLE SourceTbl ([Code] varchar(3), [Total] decimal, [Date] datetime );

INSERT INTO SourceTbl ([Code], [Total], [Date]) 
VALUES ('AA', 100, '2012-12-01'), ('AA', 200, '2013-02-01'), ('BB', 50, '2012-01-01');

A simple select will return

Code | Total | Date
'AA' | 100   | 2012-12-01
'AA' | 200   | 2013-02-01
'BB' | 50    | 2012-01-01

but what I need is the following

Code | Total | Date       | Total | Date
'AA  | 200   | 2013-02-01 | 100   | 2012-12-01
'BB  | 50    | 2012-01-01 | null  | null

I have been trying to do this using a PIVOT operator but without success (based on the question SQL Server Pivot multiple columns based on one column).

Using that example, all I get are two rows with null values.

The Total/Date columns can be repeated 13 times and they must be ordered by Date DESC.

SQL Fiddle: http://sqlfiddle.com/#!3/f37a1/2

Any help is appreciated! Thanks!

`PIVOT` + `Row_NUMBER()` to the rescue. – Hart CO Sep 06 '13 at 17:46 — Hart CO, Sep 06 '13 at 17:46

score 2 · Accepted Answer · answered Sep 06 '13 at 18:40

If you need just two columns:

with cte as (
    select *, row_number() over(partition by Code order by Date) as rn
    from SourceTbl
)
select
    code,
    max(case when rn = 1 then Total end) as Total1,
    max(case when rn = 1 then Date end) as Date1,
    max(case when rn = 2 then Total end) as Total2,
    max(case when rn = 2 then Date end) as Date2
from cte
group by code

=> sql fiddle demo

dynamic solution:

declare @stmt nvarchar(max)

;with cte as (
     select distinct
         cast(row_number() over(partition by Code order by Date) as nvarchar(max)) as rn
     from SourceTbl
)
select @stmt = isnull(@stmt + ', ', '') + 
    'max(case when rn = ' + rn + ' then Total end) as Total' + rn + ',' +
    'max(case when rn = ' + rn + ' then Date end) as Date' + rn 
from cte
order by rn

select @stmt = '
    with cte as (
        select *, row_number() over(partition by Code order by Date) as rn
        from SourceTbl
    )
    select
        code, ' + @stmt + ' from cte group by code'

exec sp_executesql
    @stmt = @stmt

=> sql fiddle demo

Awesome work! Never knew you could dynamically add columns like that. — user2600629, Sep 06 '13 at 19:05

score 0 · Answer 2 · edited Sep 06 '13 at 18:29

0

Are you trying to dynamically create columns in your result set?

If you had a third record of 'AA' with a total of 300 and the date of 03/01/2013 would you that mean you would want something like this displayed?

Code | Total | Date       | Total | Date      | Total | Date
 AA  | 200   | 2013-02-01 | 100   | 2012-12-01| 300   | 03-01-13
 BB  | 50    | 2012-01-01 | null  | null      | null  | null

edited Sep 06 '13 at 18:29

CrckrJack

93
5

answered Sep 06 '13 at 18:21

user2600629

559
6
16

1

This should be a comment rather than answer – Sonam Sep 06 '13 at 18:38
Yes i can have up to 13 total/date combinations. – Alex Costa Sep 07 '13 at 18:41

SQL Server Rows to Multi-Columns

2 Answers2