PHP SQL Server - dynamic table rows and columns?

Question

I've got a similar question to a previous question of mine - Pivot Tables PHP/MySQL

In that query I knew that the row headers would be called and just needed to dynamically create the column headers. Now, however, I don't know the row headers either.

So I have a query

select eng, count, weekof from dbo.RPT_ENG_WEEK ('2013-03-03', '2013-03-16', '2013-03-03', '2013-03-16') order by eng, weekof asc

that is returning the following data:

eng     count   weekof
James   11      2013-03-03  
James   12      2013-03-10  
Bill    2       2013-03-03  
Gary    15      2013-03-03  
Gary    5       2013-03-10  
Fred    3       2013-03-03  
Fred    2       2013-03-10

So I'm then wanting to turn this into one row per eng and one column per weekof like below

week:  2013-03-03    2013-03-10
James:     11              12       
Bill:      2               0         
Gary:      15              5            
Fred:      3               2

The answer I got in my previous question works a charm if I were to put in the names of the eng as static rows, but there are, potentially, 100 or so eng it could return and really don't want to have to manually keep updating the list!

I could probably work it out via a cursor in the SQL function, but is there a better way of doing it?

Answer 1

Transforming data from rows into columns is known as a PIVOT. Since you are using SQL Server, then you can use the pivot function to get the result.

There are a few ways that you can do this. If you have a known number of weekof values, then you can hard-code the query:

select eng, 
  coalesce([2013-03-03], 0) [2013-03-03],  
  coalesce([2013-03-10], 0) [2013-03-10]
from
(
  select eng, [count], weekof
  from RPT_ENG_WEEK
) d
pivot
(
  sum([count])
  for weekof in ([2013-03-03], [2013-03-10])
) piv;

See SQL Fiddle with Demo.

But if you have an unknown number of values, then you will want to implement dynamic SQL to get the result:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(cast(weekof as date)) 
                    from RPT_ENG_WEEK
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT distinct ', coalesce(' + QUOTENAME(cast(weekof as date))+', 0) as '+ QUOTENAME(cast(weekof as date))
                    from RPT_ENG_WEEK
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT eng, ' + @colsNull + ' 
            from 
            (
                select eng, [count], cast(weekof as date) weekof
                from RPT_ENG_WEEK
            ) x
            pivot 
            (
                sum([count])
                for weekof in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. Both give the result:

|   ENG | 2013-03-03 | 2013-03-10 |
-----------------------------------
|  Bill |          2 |          0 |
|  Fred |          3 |          2 |
|  Gary |         15 |          5 |
| James |         11 |         12 |