Transposing data into multiple columns

Question

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

possible duplicate of Get ROWS as COLUMNS (SQL Server dynamic PIVOT query) — RichardTheKiwi
– RichardTheKiwi, Commented May 3, 2013 at 9:57

Taryn · Accepted Answer · 2012-11-02 17:34:41Z

5

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

edited Nov 2, 2012 at 17:34

answered Sep 20, 2012 at 16:05

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dbinott Over a year ago

Thanks for the 2nd option. Helped out tremendously.

Collectives™ on Stack Overflow

Transposing data into multiple columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related