SQL Pivot display data along with identity column or Select multiple columns from table but Group By one

• sqldevlearn

  Old Hand

  Points: 397

  More actions

  March 6, 2018 at 2:58 am

  Go to Answer

  #333841

  I have my fiddle here which is working fine, but I need the identity column of each value from table2 and append it to the result some thing like 1 - 4.00, 6 - 8.00.Can some one help me.

  My current working fiddle
  http://sqlfiddle.com/#!18/4c628/1
  CREATE TABLE Table1  ([TaskId] int, [TaskName] varchar(200));
CREATE TABLE Table2  ([WorkId] int, [TaskId] int, [Workday ] datetime, WorkHours decimal(18,2));
INSERT INTO Table1  ([TaskId], [TaskName])VALUES  (1,'Test Task'),  (2,'Test Task1'),  (3,'Test Task2');
INSERT INTO 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'
declare @sdate varchar(10) = (select convert(varchar(10),@startdate,120))
declare @edate varchar(10) = (select convert(varchar(10),@startdate + 7,120))
DECLARE @cols AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)
declare @sql varchar(max) = 'select TASKNAME   ,
max(isnull(cast([1] as char(5)),'''')) as [SUN '+datename(d,@startdate)+']   ,
max(isnull(cast([2] as char(5)),'''')) as [MON '+datename(d,@startdate + 1)+']   ,
max(isnull(cast([3] as char(5)),'''')) as [TUE '+datename(d,@startdate + 2)+']   ,
max(isnull(cast([4] as char(5)),'''')) as [WED '+datename(d,@startdate + 3)+']   ,
max(isnull(cast([5] as char(5)),'''')) as [THU '+datename(d,@startdate + 4)+']   ,
max(isnull(cast([6] as char(5)),'''')) as [FRI '+datename(d,@startdate + 5)+']   ,
max(isnull(cast([7] as char(5)),'''')) as [SAT '+datename(d,@startdate + 6)+']   ,
sum(isnull([1],0)+isnull([2],0)+isnull([3],0)+isnull([4],0)+isnull([5],0)+isnull([6],0)+isnull([7],0)) as [TOTAL]
from (select t1.taskid,t1.taskname,WorkId,datepart(dw,t2.workday) wd,t2.workhours from Table1 t1 inner join
Table2 t2 on t1.taskid = t2.taskid where t2.workday between '''+@sdate+''' and '''+@edate+''')spivot( sum(workhours)
for wd in ([1],[2],[3],[4],[5],[6],[7])) pGROUP BY taskid,TASKNAME'
exec(@sql)

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  March 6, 2018 at 9:29 am

  Answer

  #1982096

  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