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 |
