TSql Complex Pivot

Question

I have a table like this...

LEVEL        Action         Date             User
--------------------------------------------------
1            Approve        01/01/2013       User1
2            Approve        02/01/2013       User2
3            Rejected       03/01/2013       User3
1            Approve        04/01/2013       User1
2            Approve        05/01/2013       User2
3            Approve        06/01/2013       User3
.                .              .              .
.                .              .              .
.                .              .              .

And I want this...

Is this possible using PIVOT?

LEVEL1 - User 1           LEVEL2 - User 2                  LEVEL3 - User 3
---------------------------------------------------------------------------
01/01/2013 - Approve      02/01/2013 - Approve             03/01/2013 - Rejected
04/01/2013 - Approve      05/01/2013 - Approve             06/01/2013 - Approve
         .                        .                                .
         .                        .                                .

Note : Number of Level are dynamic. e.g. It can be 5 levels, 6 level etc to fully approve one item. So the number of columns in Pivoted table is dynamic.

Answer 1

Yes, this can be done using the PIVOT function, I would first suggest looking at a hard-coded version of the query so you can see how the query is written before moving to a dynamic version of the query.

A static version will be similar to the following:

select [Level1 - User1], [Level2 - User2], [Level3 - User3]
from
(
  select 'Level'+cast(level as varchar(1)) + ' - '+ [user] col, 
    convert(varchar(10), date, 101) +' - '+ action value,
    row_number() over(partition by level order by [user], date) rn
  from yt
) d
pivot
(
  max(value)
  for col in ([Level1 - User1], [Level2 - User2], [Level3 - User3])
) piv;

See SQL Fiddle with Demo. You will notice that the level and user columns are concatenated to create the new columns, and the date and action are concatenated to create the value for each column. I also added a row_number() to create a unique value for each row, this will be important when you apply the aggregate function in the PIVOT. If you do not use this, then you will get only one row as a result.

Since you now have a working version, this can be converted to a dynamic version easily:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Level'+cast(level as varchar(1)) + ' - '+ [user]) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                select ''Level''+cast(level as varchar(1)) + '' - ''+ [user] col, 
                  convert(varchar(10), date, 101) +'' - ''+ action value,
                  row_number() over(partition by level order by [user], date) rn
                from yt
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. The result for both is:

|       LEVEL1 - USER1 |       LEVEL2 - USER2 |        LEVEL3 - USER3 |
-----------------------------------------------------------------------
| 01/01/2013 - Approve | 02/01/2013 - Approve | 03/01/2013 - Rejected |
| 04/01/2013 - Approve | 05/01/2013 - Approve |  06/01/2013 - Approve |