pivot table to make each date as column with out any aggregate column

Question

I have following query which give me a row for each day earning for each employee.

Now i want to show those date rows as columns . my current query and its output is as follow.

declare @StartDate datetime,@EndDate datetime,@CompanyId int
set @StartDate='01/01/2013'
set @EndDate='01/31/2013'
set @CompanyId=3


;with d(date) as (
  select cast(@StartDate as datetime)
  union all
  select date+1
  from d
  where date < @EndDate
  )
select distinct d.date CDate,E.EmployeeId,Earning.EarningDescription,Earning.EarningId
,E.FirstName + ' ' + E.MiddleName + ' ' + E.LastName AS EmployeeName
from d,Employee as E
inner join Earning on E.CompanyId=Earning.CompanyId
where E.CompanyId=@CompanyId and Earning.IsOnTimeCard=1 and Earning.IsHourly=1 
order by EmployeeId,CDate,EarningId

current query output

This output need to be converted using pivot. i have tried by looking into some examples of pivot .

As per suggested answers to look into for solutions, now i have this query and its giving me error

declare @StartDate datetime,@EndDate datetime,@CompanyId int,@cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
set @StartDate='01/01/2013'
set @EndDate='01/31/2013'
set @CompanyId=3



declare @WorkingDays Table 
 (
   WDate smalldatetime
 )


;with d(date) as (
  select cast(@StartDate as datetime)
  union all
  select date+1
  from d
  where date < @EndDate
  )
  insert into @WorkingDays select  d.date from d

  SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(wd.WDate) 
            FROM @WorkingDays wd
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

  PRINT @cols

 set @query = ' 
SELECT
*
FROM
(
    select distinct WDate CDate,E.EmployeeId,Earning.EarningDescription,Earning.EarningId

    from  @WorkingDays  ,Employee as E
    inner join Earning on E.CompanyId=Earning.CompanyId
    where E.CompanyId=@CompanyId and Earning.IsOnTimeCard=1 and Earning.IsHourly=1 
) src
PIVOT
(
    MIN(src.EarningId)
    FOR src.CDate IN ('+@cols+')
) AS PivotedView '

PRINT (@query)
execute(@query)

and error is as follow now

Must declare the table variable "@WorkingDays".

nothing wring with those examples but i am not able to do that . — rahularyansharma, Dec 20 '13 at 05:52
just worked in similar problem here, http://stackoverflow.com/questions/20493041/generate-a-range-of-date-and-compare-with-another-range-of-date-and-pivot-it/20656261#20656261 — KumarHarsh, Dec 20 '13 at 06:28
@hubsonbropa problem with pivot examples are they are for static number of column but in my case number of columns always change as date range changed . — rahularyansharma, Dec 20 '13 at 07:09

score 1 · Accepted Answer · edited May 23 '17 at 12:19

1

The SQL Server PIVOT clause does not support dynamic columns, and it looks like you require a dynamic column list. The only way to do this using PIVOT is to construct a dynamic SQL statement, pivoting the the list of dates required at that time, and then execute this SQL.

A similar solution is presented in Pivot Dynamic Columns, no Aggregation

edited May 23 '17 at 12:19

Community

1
1

answered Dec 20 '13 at 07:20

Daniel B

797
4
13

You cannot reference the table variable @WorkingDays from inside the dynamic SQL statement. – Daniel B Dec 20 '13 at 08:07
Thanks for your link , i have solved the query and its working for me. I also posted resulted query as answer . – rahularyansharma Dec 20 '13 at 10:24

score 0 · Answer 2 · answered Dec 20 '13 at 10:16

declare @StartDate datetime,@EndDate datetime,@CompanyId int,@cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
set @StartDate='01/01/2013'
set @EndDate='01/31/2013'
set @CompanyId=3



 Create table #t
   (
     WDate smalldatetime
   )





;with d(date) as (
  select cast(@StartDate as datetime)
  union all
  select date+1
  from d
  where date < @EndDate
  )
  insert into #t select  d.date from d

  SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(wd.WDate) 
            FROM #t wd
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

  PRINT @cols

 set @query = ' 
SELECT
*
FROM
(
    select distinct WDate CDate,E.EmployeeId,Earning.EarningDescription,Earning.EarningId
    ,E.FirstName +'  + '''' + ' ''' + '+ E.MiddleName +'  + '''' + ' ''' + '+ E.LastName AS EmployeeName
    from  #t  ,Employee as E
    inner join Earning on E.CompanyId=Earning.CompanyId
    where E.CompanyId='+ CAST (@CompanyId as nvarchar(50)) +' and Earning.IsOnTimeCard=1 and Earning.IsHourly=1 
) src
PIVOT
(
    MIN(EarningDescription)
    FOR src.CDate IN ('+@cols+')
) AS PivotedView order by EmployeeId,EarningId '

PRINT (@query)
execute(@query)

drop table #t

score -2 · Answer 3 · answered Dec 20 '13 at 06:56

Try below query

 select '01/01/2013' Cdate, 19 EmployeeID,'regular' EarningDescription, 21 EarningID, CAST('rebecca smith' as varchar(20)) Employeename INTO #temp union all select
 '01/01/2013', 19,'OverTime', 21, 'rebecca smith'  union all select
 '01/01/2013', 19,'DoubleOvertime', 22, 'rebecca smith'  union all select
 '01/01/2013', 19,'regular', 23, 'rebecca smith'  union all select
 '01/01/2013', 19,'vacation', 104, 'rebecca smith'  union all select
 '01/01/2013', 19,'Travel', 105, 'rebecca smith'  union all select
 '02/01/2013', 19,'regular', 21, 'rebecca smith'  union all select
 '02/01/2013', 19,'OverTime', 21, 'rebecca smith'  union all select
 '02/01/2013', 19,'DoubleOvertime', 22, 'rebecca smith'  union all select
 '02/01/2013', 19,'regular', 23, 'rebecca smith'  union all select
 '02/01/2013', 19,'vacation', 104, 'rebecca smith'  union all select
 '02/01/2013', 19,'Travel', 105, 'rebecca smith'   union all select
 '03/01/2013', 19,'regular', 21, 'rebecca smith'  union all select  
 '03/01/2013', 19,'OverTime', 21, 'rebecca smith'  union all select
 '03/01/2013', 19,'DoubleOvertime', 22, 'rebecca smith'  union all select
 '03/01/2013', 18,'regular', 23, 'ganesh'  union all select
 '03/01/2013', 18,'vacation', 104, 'ganesh'  union all select
 '03/01/2013', 18,'Travel', 105, 'ganesh'  
 declare @StartDate datetime,@EndDate datetime,@CompanyId int
 set @StartDate='01/01/2013'
 set @EndDate='01/31/2013'
 set @CompanyId=3
 ;with d(date) as (
   select cast(@StartDate as datetime) as date
   union all
   select date+1
   from d
   where date < @EndDate
   )

   select * INTO #dates FROM d
   declare @dates varchar(max), @datecolumn varchar(max) 
   select @dates=COALESCE(@dates+',','')+'['+convert(varchar(MAX),date,103)+']' from #dates
   print @dates
   EXEC(
  ' SELECT * FROM (
      select distinct  convert(varchar(MAX),CDate,103) CDate,E.EmployeeId,e.EarningDescription,e.EarningId
      , EmployeeName
      from #temp e
      ) as x
      PIVOT  (
      MAX(EmployeeName)
 FOR CDate IN ('+@dates+')
 ) as pvt order by EmployeeId,EarningId')

don't forget to make as answer if it is working fine for you.

pivot table to make each date as column with out any aggregate column

3 Answers3