Pivot Query - SQL Server 2005

Question

I have the following query in SQL Server 2005 that fetches the result that is mentioned below it.

select Distinct(date),id,
           sum(convert(float,Gross)),
           count(*) as no from Daily
    group by date,id 
    order by date desc

Date                     id          Gross          Count
2012-11-25 00:00:00.000  Client id1  1232.6140752   12
2012-11-25 00:00:00.000  Client id2  1183.75621528  88
2012-11-26 00:00:00.000  Client id3  4561.459086    67
2012-11-26 00:00:00.000  Client id4  6781.15660608  440

Now how do I get the result in the following format. This looks like a pivot query is reqd please help

id           Date1  Date2  Date3  Date4  Date5 Date6 Date7
Client id1   Gross       
Client id2   Gross
Client id3   Gross

Answer 1

There are two ways that you can PIVOT, either a static version where you hard-code all of the date values or a dynamic version which will generate the list of dates at run-time.

Static Version:

If you had a limited number of dates your query will be similar to this.

select id, [yourDate1], [yourDate2], [yourDate3]
from
(
  select date, id, cast(gross as float) as gross
  from Daily
) src
pivot
(
  sum(gross)
  for date in ([yourDate1], [yourDate2], [yourDate3])
) piv;

See SQL Fiddle with Demo

Dynamic Version:

This version generates dynamic SQL to get the list of dates at run-time. Your code will be similar to this:

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

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

set @query = 'SELECT id, ' + @cols + ' from 
             (
               select date, id, cast(gross as float) as gross
               from Daily
            ) src
            pivot 
            (
                sum(gross)
                for date in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Answer 2

WITH a AS
(
    SELECT [Id], [Gross]
    , [DateRank] = DENSE_RANK() OVER (ORDER BY [Date])
    FROM [Daily]
)
SELECT *
FROM a PIVOT
(
    SUM([Gross])
    FOR [DateRank] IN ([1], [2], [3], [4], [5], [6], [7])
) b