Sql Pivot Table With Three Cross Tab And Multiple Columns Dynamically

I have the following table with values as CREATE TABLE stud ( sname NVARCHAR(10), hr NVARCHAR(30), dt DATETIME, att VARCHAR(3) ) INSERT INTO

Solution 1:

If you are using SQL Server 2005+, then there are several ways that you can apply the PIVOT function.

You can hard-code the values in the form of a static pivot:

select*from
(
  select sname, 
    'hour_no_'+hr+'_'+convert(nvarchar(10), dt, 120) dt,
    att
  from stud
) st
pivot
(
  max(att)
  for dt in ([hour_no_1_2013-01-21], [hour_no_2_2013-01-21],
             [hour_no_1_2013-01-22], [hour_no_2_2013-01-22])
) piv

See SQL Fiddle with Demo

Or you can use dynamic sql to generate the sql statement at run-time. The dynamic version of the query is:

DECLARE@colsAS NVARCHAR(MAX),
    @queryAS NVARCHAR(MAX)

select@cols= STUFF((SELECT', '+ QUOTENAME('Hour_No_'+hr+'_'++convert(nvarchar(10), dt, 120)) 
           from stud
           groupby hr, dt
           orderby dt, hr
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set@query='SELECT sname,'+@cols+' from 
             (
                select sname, 
                  ''hour_no_''+hr+''_''+convert(nvarchar(10), dt, 120) dt,
                  att
                from stud
            ) x
            pivot 
            (
                max(att)
                for dt in ('+@cols+')
            ) p 'execute(@query)

See SQL Fiddle with Demo.

Both give the result:

| SNAME | HOUR_NO_1_2013-01-21 | HOUR_NO_2_2013-01-21 | HOUR_NO_1_2013-01-22 | HOUR_NO_2_2013-01-22 |
-----------------------------------------------------------------------------------------------------
|   Abi |                    a |                    p |                   ml |                   od |
|  bala |                    p |                    a |                   od |                   ml |