SQL Transpose rows to columns (group by key variab

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...AMOUNT_1...TYPE_1...AMOUNT_2...TYPE_2...AMOUNT_3...TYPE_3
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.

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