Post reply

Pivot table

  • March 21, 2016 at 9:21 am

    #309332

    Hi,

    CREATE TABLE #Status

    (

    Cycle INT,

    Name VARCHAR(20),

    Status VARCHAR(20),

    ForecastDate DATE,

    MID VARCHAR(20),

    NumberOfTasks INT

    )

    INSERT INTO #Status ( Cycle,Name,Status,ForecastDate,MID,NumberOfTasks)

    SELECT 1004,'ABCD','Passed','2016-03-15','MAC-COO-1',2 UNION

    SELECT 1014,'ABCD','No Run','2016-03-15','MAC-COO-1',1 UNION

    SELECT 1014,'ABCD','Passed','2016-03-15','MAC-COO-1',3 UNION

    SELECT 1014,'ABCD','Passed','2016-03-15','MAC-COO-1',1 UNION

    SELECT * FROM #Status

    --DROP TABLE #Status

    --DEsired result Based on Cycle,Name ,status and FocusDate Need pivot columns on Status and total column . Total column should be sum of NumberOfTasks based on Cycle,name and FocusDate

    --Status Pivot column should be sum of NumberOfTasks based on Cycle,name,status and FocusDate

    SELECT 1004 AS Cycle,'ABCD' AS NAme,'Passed' AS Status,'2016-03-15' AS ForeCast,'MAC-COO-1' AS MID,2 AS NumberOFTasks, 2 AS 'Passed', 0 As 'No Run', 2 AS Total UNION

    SELECT 1014 AS Cycle,'ABCD' AS NAme,'No Run' AS Status,'2016-03-15' AS ForeCast,'MAC-COO-1' AS MID,1 AS NumberOFTasks, 4 AS 'Passed', 1 As 'No Run', 5 AS Total UNION

    SELECT 1014 AS Cycle,'ABCD' AS NAme,'Passed' AS Status,'2016-03-15' AS ForeCast,'MAC-COO-1' AS MID,3 AS NumberOFTasks, 4 AS 'Passed', 1 As 'No Run' , 5 AS Total UNION

    SELECT 1014 AS Cycle,'ABCD' AS NAme,'Passed' AS Status,'2016-03-15' AS ForeCast,'MAC-COO-1' AS MID,1 AS NumberOFTasks, 4 AS 'Passed', 1 As 'No Run' , 5 AS Total

    Thnaks,

    PSB

  • March 21, 2016 at 10:02 am

    #1866521

    Use cross tabs with the OVER clause.

    SELECT *,

    SUM(CASE WHEN Status = 'Passed' THEN NumberOfTasks ELSE 0 END) OVER(PARTITION BY Cycle) AS [Passed],

    SUM(CASE WHEN Status = 'No Run' THEN NumberOfTasks ELSE 0 END) OVER(PARTITION BY Cycle) AS [No Run],

    SUM(NumberOfTasks) OVER(PARTITION BY Cycle) AS [Total]

    FROM #Status;

    EDIT: I only used Cycle, but you must add any columns needed to the partition.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 21, 2016 at 11:16 am

    #1866559

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply