CODEWITHSUNDEEP
sqlsql-serverpivot
user2501777
user2501777

Reputation: 33

Display multiple rows and column values into a single row, multiple column values

I have to show multiple incomes, type of income and employer name values for a single individual in a single row. So, if 'A' has three different incomes from three different sources, 

 id  | Name | Employer     |  IncomeType       | Amount

 123 |  XYZ | ABC.Inc      |   EarningsformJob |  $200.00

 123 |  XYZ | Self         | Self Employment   |  $300.00

 123 | XYZ. |  ChildSupport|   Support         |  $500.00

I need to show them as

 id | Name | Employer1 | Incometype1| Amount1 | Employer2 | incometype2 | Amount2| Employer3 | Incometype3| Amount3.....

 123 |XYZ | ABC.Inc |EarningsformJob |  $200.00|Self  | Self Employment  |  $300.00|ChildSupport|   Support |  $500.00.....

I need both 'fixed number of columns' (where we know how many times employer, incometype and amount colums are going to repeat)logic and 'dynamic display of columns' ( unknown number of times these columns are going to repeat)

Thanks.

Upvotes: 3

Views: 37513

Answers (1)

Taryn
Taryn

Reputation: 247880

Since you are using SQL Server there are several ways that you can transpose the rows of data into columns.

Aggregate Function / CASE: You can use an aggregate function with a CASE expression along with row_number(). This version would require that you have a known number of values to become columns:

select id,
  name,
  max(case when rn = 1 then employer end) employer1,
  max(case when rn = 1 then IncomeType end) IncomeType1,
  max(case when rn = 1 then Amount end) Amount1,
  max(case when rn = 2 then employer end) employer2,
  max(case when rn = 2 then IncomeType end) IncomeType2,
  max(case when rn = 2 then Amount end) Amount2,
  max(case when rn = 3 then employer end) employer3,
  max(case when rn = 3 then IncomeType end) IncomeType3,
  max(case when rn = 3 then Amount end) Amount3
from
(
  select id, name, employer, incometype, amount,
    row_number() over(partition by id order by employer) rn
  from yourtable
) src
group by id, name;

See SQL Fiddle with Demo.

PIVOT/UNPIVOT: You could use the UNPIVOT and PIVOT functions to get the result. The UNPIVOT converts your multiple columns of Employer, IncomeType and Amount into multiples rows before applying the pivot. You did not specific what version of SQL Server, assuming you have a known number of values then you could use the following in SQL Server 2005+ which uses CROSS APPLY with UNION ALL to unpivot:

select id, name, 
  employer1, incometype1, amount1,
  employer2, incometype2, amount2,
  employer3, incometype3, amount3
from
(
  select id, name, col+cast(rn as varchar(10)) col, value
  from
  (
    select id, name, employer, incometype, amount,
      row_number() over(partition by id order by employer) rn
    from yourtable
  ) t
  cross apply
  (
    select 'employer', employer union all
    select 'incometype', incometype union all
    select 'amount', cast(amount as varchar(50))
  ) c (col, value)
) src
pivot
(
  max(value)
  for col in (employer1, incometype1, amount1,
              employer2, incometype2, amount2,
              employer3, incometype3, amount3)
) piv;

See SQL Fiddle with Demo.

Dynamic Version: Lastly, if you have an unknown number of values then you will need to use dynamic SQL to generate the result. 

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

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

set @query = 'SELECT id, name,' + @cols + ' 
             from 
             (
                select id, name, col+cast(rn as varchar(10)) col, value
                from
                (
                  select id, name, employer, incometype, amount,
                    row_number() over(partition by id order by employer) rn
                  from yourtable
                ) t
                cross apply
                (
                  select ''employer'', employer union all
                  select ''incometype'', incometype union all
                  select ''amount'', cast(amount as varchar(50))
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. All versions give a result:

|  ID | NAME | EMPLOYER1 |     INCOMETYPE1 | AMOUNT1 |    EMPLOYER2 | INCOMETYPE2 | AMOUNT2 | EMPLOYER3 |     INCOMETYPE3 | AMOUNT3 |
-------------------------------------------------------------------------------------------------------------------------------------
| 123 |  XYZ |   ABC.Inc | EarningsformJob |     200 | ChildSupport |     Support |     500 |      Self | Self Employment |     300 |

Upvotes: 13

Related Questions