Home Forums SQL Server 2017 SQL Server 2017 - Development SQL Pivot display data along with identity column or Select multiple columns from table but Group By one RE: SQL Pivot display data along with identity column or Select multiple columns from table but Group By one

  • Looks like you found the sad & pathetic limitations of using the pivot operator... The "old school" cross tab method handles this sort of thing without batting an eye...

    Your sample code didn't have an identity column so I just used WorkId instead... Swapping out to a different column should be easy enough though...

    IF OBJECT_ID('tempdb..#Table1', 'U') IS NOT NULL
    BEGIN DROP TABLE #Table1; END;

    CREATE TABLE #Table1 (TaskId int, TaskName varchar(200));
    INSERT #Table1 (TaskId, TaskName) VALUES (1,'Test Task'), (2,'Test Task1'), (3,'Test Task2');

    IF OBJECT_ID('tempdb..#Table2', 'U') IS NOT NULL
    BEGIN DROP TABLE #Table2; END;

    CREATE TABLE #Table2 (WorkId int, TaskId int, Workday datetime, WorkHours decimal(18,2));
    INSERT #Table2 (WorkId,TaskId, Workday,WorkHours) VALUES
      (1,1,'2018-03-04',4),
      (1,2,'2018-03-04',4),
      (2,1,'2018-03-05',2),
      (2,2,'2018-03-05',3),
      (2,3,'2018-03-05',3),
      (3,2,'2018-03-06',8),
      (4,2,'2018-03-07',8),
      (5,2,'2018-03-08',8),
      (6,2,'2018-03-09',8);

    --=======================================================================================

    DECLARE
        @startdate DATETIME = '2018-03-04',
        @debug BIT = 0;

    DECLARE
        @cols NVARCHAR(4000) = N'',
        @sql NVARCHAR(4000) = N''

    SELECT
        @cols = CONCAT(@cols, CHAR(13), CHAR(10), CHAR(9), N'[', dx.day_name, N' ', dx.day_of_month, N'] = ISNULL(NULLIF(CONCAT(SUM(CASE WHEN dw.day_of_week = ',
            dx.day_of_week, N' THEN t2.WorkHours ELSE NULL END), '' '', MIN(CASE WHEN dw.day_of_week = ',
            dx.day_of_week, N' THEN t2.WorkId ELSE NULL END), '' - '', MAX(CASE WHEN dw.day_of_week = ',
            dx.day_of_week, N' THEN t2.WorkId ELSE NULL END)), '' - ''), ''''),')
        --*
    FROM
        ( VALUES (0),(1),(2),(3),(4),(5),(6) ) dta (days_to_add)
        CROSS APPLY ( VALUES ( DATEADD(DAY, dta.days_to_add, @startdate)) ) dv (date_val)
        CROSS APPLY ( VALUES (DATEPART(WEEKDAY, dv.date_val), UPPER(LEFT(DATENAME(WEEKDAY, dv.date_val), 3)), DATEPART(DAY, dv.date_val)) ) dx (day_of_week, day_name, day_of_month)

    --PRINT(@cols)

    SET @sql = CONCAT(N'
    SELECT
        t1.TaskName,',
        @cols, N'
        [TOTAL] = ISNULL(NULLIF(CONCAT(SUM(t2.WorkHours), '' '', MIN(t2.WorkId), '' - '', MAX(t2.WorkId)), '' - ''), '''')
    FROM
        #Table1 t1
        JOIN #Table2 t2
            ON t1.TaskId = t2.TaskId
        CROSS APPLY ( VALUES (DATEPART(WEEKDAY, t2.Workday)) ) dw (day_of_week)
    WHERE
        t2.Workday >= @_startdate
        AND t2.Workday < DATEADD(DAY, 7, @_startdate)
    GROUP BY
        t1.TaskId,
        t1.TaskName
    ORDER BY
        t1.TaskId;');

    IF @debug = 1
    BEGIN
        PRINT(@sql);
    END;
    ELSE
    BEGIN
        EXEC sys.sp_executesql @sql, N'@_startdate DATETIME', @_startdate = @startdate;
    END;

    Hope this helps,
    Jason