SQL Pivot table by date

Question

i have a table like this;

I want to display my data like this;

PersonelName------EnterDate1------EnterDate2------EnterDate3------EnterDateN
   Michael------------6---------------8----------------7----------TotalWorkHour
   Jason--------------5---------------8----------------6----------TotalWorkHour
   Terra--------------6---------------6----------------6----------TotalWorkHour
   Amelie-------------8---------------8----------------7----------TotalWorkHour

EnterDates must be dynamic and with two days between. I had a personnel table.

I cant figurate this logic. I cant understand pivot. Please help me. Thanks for now.

Do you want a dynamic number of columns returned? That can't be done, a SELECT always return a specific number of columns, independent of any data. — jarlh, Sep 08 '15 at 10:07
If you are presenting the data in a C# solution of some sorts, I would try to do the pivoting in code, not in sql. — larsts, Sep 08 '15 at 10:20

score -1 · Answer 1 · answered Sep 08 '15 at 11:25

If you want to do a dynamic pivot, you must use dynamic SQL

Assuming you want to pivot by date and not date/time, the SQL will look something like:-

create procedure dbo.[Personnel_Pivot](@fromDate date,@toDate date)
as
begin
    SET NOCOUNT ON;
    DECLARE @LoopDate date,@MaxDate date,@InSQL VARCHAR(max),@InSQL2 VARCHAR(max),@BigSql VARCHAR(max)
    SELECT @MaxDate = max(enterdate),@LoopDate = min(enterdate) from [dbo].[PersonnelLog] where cast(enterdate as date) between @fromDate and @toDate
    WHILE @LoopDate <= @MaxDate
    BEGIN
        SET @InSQL = COALESCE(@InSQL + ',','') + '[' + convert(varchar(10),@Loopdate,103) + ']'
        SET @InSQL2 = COALESCE(@InSQL2 + ',','') + '[' + convert(varchar(10),@Loopdate,103)+ ']'
        --print @i
        set @LoopDate= (select distinct MIN(enterdate) from [dbo].[PersonnelLog] where EnterDate>@Loopdate and cast(enterdate as date) between @fromDate and @toDate)
    END
    --print @InSQL2
    set @BigSql=
    '
    select PersonnelID,'+@InSQL2+'
    from
        (
        select convert(varchar(10),cast(enterdate as date),103) as EnterDate,PersonnelID
        from [dbo].[PersonnelLog] 
        ) p
    PIVOT
        (
        count(EnterDate)
        for EnterDate in ('+@InSQL+')
        ) as p2
    '
    --print @BigSQL
    EXECUTE (@BigSQL)
end

Hopefully you should be able to work it out from there

SQL Pivot table by date

1 Answers1