Transposing data into multiple columns
pivot, sql, sql-server
Solution
You will want to use the `PIVOT` function for this. If you know the number of columns to transform then you can hard-code it via a static pivot:
select *
from
(
select client, activity, dt
from yourtable
) x
pivot
(
max(dt)
for activity in ([A], [B], [C], [D])
) p
see SQL Fiddle with Demo
If you have an unknown number of columns then use a dynamic version:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(activity)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT client,' + @cols + ' from
(
select client, activity, dt
from yourtable
) x
pivot
(
max(dt)
for activity in (' + @cols + ')
) p '
execute(@query)
see SQL Fiddle with Demo
Finally, this can also be done with a `CASE` statement and an aggregate function (See SQL Fiddle with Demo):
select client,
max(case when activity = 'A' then dt end) as A,
max(case when activity = 'B' then dt end) as B,
max(case when activity = 'C' then dt end) as C,
max(case when activity = 'D' then dt end) as D
from yourtable
group by client
Problem
I have numbers of clients and each client has 67 records. The record contains Activities, Dates, IDs, flags and many other columns. I would like the Activities names to be the headings and Dates underneath to those Activity headings. I am trying to explain it more using the tables below. This is the current output: ``` Client Activity Date 1 A 21/15 1 B 5/5/2012 1 C 51/3115 1 D 54/6/84 2 A 8/6/99 2 B 1/1/2011 2 C 8/4 2 D 9/81/1 3 A 6/51/8 3 B 1/61/8 3 C 1/31 3 D 3/2/1 ``` And I would like it to be: ``` Client A B C D 1 21/15 5/5/2012 51/31/15 54/6/84 2 8/6/99 1/1/2011 8/4 9/81/1 3 6/51/8 1/61/8 1/31 3/2/1 ```