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.

Dornick

Search for dynamic pivot and you'll find quite a few answers. It's not so much about aggregation as the creation of the right column names and the value within them. – Genro

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) |

Placentation

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;

Ifni

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Bim