SQL Transpose rows to columns (group by key variable)?

Question

I am trying to transpose rows into columns, grouping by a unique identifier (CASE_ID).

I have a table with this structure:

CASE_ID   AMOUNT   TYPE  
100         10       A  
100         50       B  
100         75       A  
200         33       B  
200         10       C  

And I am trying to query it to produce this structure...

| CASE_ID | AMOUNT1 | TYPE1 | AMOUNT2 | TYPE2 | AMOUNT3 |  TYPE3 |
|---------|---------|-------|---------|-------|---------|--------|
|     100 |      10 |     A |      50 |     B |      75 |      A |
|     200 |      33 |     B |      10 |     C |  (null) | (null) |

(assume much larger dataset with large number of possible values for CASE_ID, TYPE and AMOUNT)

I tried to use pivot but I don't need an aggregate function (simply trying to restructure the data). Now I'm trying to somehow use row_number but not sure how.

I'm basically trying to replicate and SPSS command called Casestovars, but need to be able to do it in SQL. thanks.

Answer 1

You can get the result by creating a sequential number with row_number() and then use an aggregate function with CASE expression:

select case_id,
  max(case when seq = 1 then amount end) amount1,
  max(case when seq = 1 then type end) type1,
  max(case when seq = 2 then amount end) amount2,
  max(case when seq = 2 then type end) type2,
  max(case when seq = 3 then amount end) amount3,
  max(case when seq = 3 then type end) type3
from 
(
  select case_id, amount, type,
    row_number() over(partition by case_id
                      order by case_id) seq
  from yourtable
) d
group by case_id;

See SQL Fiddle with Demo.

If you are using a database product that has the PIVOT function, then you can use row_number() with PIVOT, but first I would suggest that you unpivot the amount and type columns first. The basic syntax for a limited number of values in SQL Server would be:

select case_id, amount1, type1, amount2, type2, amount3, type3
from
(
  select case_id, col+cast(seq as varchar(10)) as col, value
  from 
  (
    select case_id, amount, type,
      row_number() over(partition by case_id
                        order by case_id) seq
    from yourtable
  ) d
  cross apply
  (
    select 'amount', cast(amount as varchar(20)) union all
    select 'type', type
  ) c (col, value)
) src
pivot
(
  max(value)
  for col in (amount1, type1, amount2, type2, amount3, type3)
) piv;

See SQL Fiddle with Demo.

If you have an unknown number of values, then you can use dynamic SQL to get the result - SQL Server syntax would be:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by case_id
                                                order by case_id) seq
                      from yourtable
                    ) d
                    cross apply
                    (
                      select 'amount', 1 union all
                      select 'type', 2
                    ) c (col, so)
                    group by col, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT case_id,' + @cols + ' 
             from 
             (
                select case_id, col+cast(seq as varchar(10)) as col, value
                from 
                (
                  select case_id, amount, type,
                    row_number() over(partition by case_id
                                      order by case_id) seq
                  from yourtable
                ) d
                cross apply
                (
                  select ''amount'', cast(amount as varchar(20)) union all
                  select ''type'', type
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Each version will give the result:

| CASE_ID | AMOUNT1 | TYPE1 | AMOUNT2 | TYPE2 | AMOUNT3 |  TYPE3 |
|---------|---------|-------|---------|-------|---------|--------|
|     100 |      10 |     A |      50 |     B |      75 |      A |
|     200 |      33 |     B |      10 |     C |  (null) | (null) |

Answer 2

Below is the solution in big query:

DECLARE cols STRING;
DECLARE query STRING;

SET cols = (
  SELECT STRING_AGG(DISTINCT CONCAT(column, CAST(seq AS STRING)), ',')
  FROM (
    SELECT column, ROW_NUMBER() OVER(PARTITION BY case_id ORDER BY case_id) AS seq
    FROM yourtable
    CROSS JOIN UNNEST(['amount', 'type']) AS column
  )
);

SET query = (
  SELECT CONCAT(
    'SELECT case_id, ', cols, ' ',
    'FROM (',
    '  SELECT case_id, CONCAT(column, CAST(seq AS STRING)) AS column, value ',
    '  FROM (',
    '    SELECT case_id, amount, type, ',
    '      ROW_NUMBER() OVER(PARTITION BY case_id ORDER BY case_id) AS seq ',
    '    FROM yourtable',
    '  ) ',
    '  CROSS JOIN UNNEST(["amount", "type"]) AS column ',
    '  CROSS JOIN (',
    '    SELECT "amount" AS column, CAST(amount AS STRING) AS value FROM yourtable UNION ALL ',
    '    SELECT "type" AS column, type AS value FROM yourtable ',
    '  ) ',
    '  WHERE column = CONCAT(column, CAST(seq AS STRING))',
    ') ',
    'PIVOT(MAX(value) FOR column IN (', cols, '))'
  )
);

EXECUTE IMMEDIATE query;