none
TSQL - Pivot

    Question

  • Date    Count    Status
    11/27/2012    13    Failure
    11/27/2012    275    Success
    11/28/2012    13    Failure
    11/28/2012    549    Success
    11/29/2012    59    Failure
    11/29/2012    577    Success
    11/30/2012    2    Failure
    11/30/2012    579    Success
    12/01/2012    6    Failure
    12/01/2012    558    Success
    12/02/2012    2    Failure
    12/02/2012    466    Success
    12/03/2012    1    Failure
    12/03/2012    280    Success

    I need result as follows.

    Date    Success    Failure    Total
    11/27/2012    275    13    288
    11/28/2012    549    13    562
    11/29/2012    577    59    636
    11/30/2012    579    2    581
    12/01/2012    558    6    564
    12/02/2012    466    2    468

    Any help?
    Monday, December 03, 2012 11:08 AM

Answers

  • with data([Date],[Count],Status) as
    (
    	select * from ( values 
    		('11/27/2012',    13 ,  'Failure'),
    		('11/27/2012',    275,  'Success'),
    		('11/28/2012',    13 ,  'Failure'),
    		('11/28/2012',    549,  'Success'),
    		('11/29/2012',    59 ,  'Failure'),
    		('11/29/2012',    577,  'Success'),
    		('11/30/2012',    2  ,  'Failure'),
    		('11/30/2012',    579,  'Success'),
    		('12/01/2012',    6  ,  'Failure'),
    		('12/01/2012',    558,  'Success'),
    		('12/02/2012',    2  ,  'Failure'),
    		('12/02/2012',    466,  'Success'),
    		('12/03/2012',    1  ,  'Failure'),
    		('12/03/2012',    280,  'Success')
    	)  data([Date], [Count], Status)
    )
    select *, Total = Success + Failure
    from ( select * from data d1 pivot( sum([Count]) for Status in ([Success],[Failure]) ) as PivotData ) d

    Date       Success     Failure     Total
    ---------- ----------- ----------- -----------
    11/27/2012 275         13          288
    11/28/2012 549         13          562
    11/29/2012 577         59          636
    11/30/2012 579         2           581
    12/01/2012 558         6           564
    12/02/2012 466         2           468
    12/03/2012 280         1           281




    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer




    Monday, December 03, 2012 11:38 AM
  • DECLARE @T TABLE(Dt DATE,CNT INT,[Status] VARCHAR(20))
    INSERT INTO @T 
    SELECT '11/27/2012',    13   , 'Failure' UNION ALL
    SELECT '11/27/2012',    275  ,  'Success' UNION ALL
    SELECT '11/28/2012',    13   , 'Failure'UNION ALL
    SELECT '11/28/2012',    549  ,  'Success'UNION ALL
    SELECT '11/29/2012',    59  ,  'Failure'UNION ALL
    SELECT '11/29/2012',    577 ,   'Success'UNION ALL
    SELECT '11/30/2012',    2   , 'Failure'UNION ALL
    SELECT '11/30/2012' ,   579 ,   'Success'UNION ALL
    SELECT '12/01/2012',    6   , 'Failure'UNION ALL
    SELECT '12/01/2012',    558,    'Success'UNION ALL
    SELECT '12/02/2012',    2,    'Failure'UNION ALL
    SELECT '12/02/2012',    466,    'Success'UNION ALL
    SELECT '12/03/2012',    1,    'Failure'UNION ALL
    SELECT '12/03/2012',    280,    'Success'
    
    
    SELECT 
    Dt,
    Success,
    Failure,
    Success + Failure AS Total
    FROM
    (SELECT * FROM @T )P
    PIVOT 
    (SUM(CNT) FOR [STATUS] IN (Failure,Success))PVT
    


    Thanks and regards, Rishabh K

    Monday, December 03, 2012 11:41 AM

All replies

  • with data([Date],[Count],Status) as
    (
    	select * from ( values 
    		('11/27/2012',    13 ,  'Failure'),
    		('11/27/2012',    275,  'Success'),
    		('11/28/2012',    13 ,  'Failure'),
    		('11/28/2012',    549,  'Success'),
    		('11/29/2012',    59 ,  'Failure'),
    		('11/29/2012',    577,  'Success'),
    		('11/30/2012',    2  ,  'Failure'),
    		('11/30/2012',    579,  'Success'),
    		('12/01/2012',    6  ,  'Failure'),
    		('12/01/2012',    558,  'Success'),
    		('12/02/2012',    2  ,  'Failure'),
    		('12/02/2012',    466,  'Success'),
    		('12/03/2012',    1  ,  'Failure'),
    		('12/03/2012',    280,  'Success')
    	)  data([Date], [Count], Status)
    )
    select *, Total = Success + Failure
    from ( select * from data d1 pivot( sum([Count]) for Status in ([Success],[Failure]) ) as PivotData ) d

    Date       Success     Failure     Total
    ---------- ----------- ----------- -----------
    11/27/2012 275         13          288
    11/28/2012 549         13          562
    11/29/2012 577         59          636
    11/30/2012 579         2           581
    12/01/2012 558         6           564
    12/02/2012 466         2           468
    12/03/2012 280         1           281




    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer




    Monday, December 03, 2012 11:38 AM
  • DECLARE @T TABLE(Dt DATE,CNT INT,[Status] VARCHAR(20))
    INSERT INTO @T 
    SELECT '11/27/2012',    13   , 'Failure' UNION ALL
    SELECT '11/27/2012',    275  ,  'Success' UNION ALL
    SELECT '11/28/2012',    13   , 'Failure'UNION ALL
    SELECT '11/28/2012',    549  ,  'Success'UNION ALL
    SELECT '11/29/2012',    59  ,  'Failure'UNION ALL
    SELECT '11/29/2012',    577 ,   'Success'UNION ALL
    SELECT '11/30/2012',    2   , 'Failure'UNION ALL
    SELECT '11/30/2012' ,   579 ,   'Success'UNION ALL
    SELECT '12/01/2012',    6   , 'Failure'UNION ALL
    SELECT '12/01/2012',    558,    'Success'UNION ALL
    SELECT '12/02/2012',    2,    'Failure'UNION ALL
    SELECT '12/02/2012',    466,    'Success'UNION ALL
    SELECT '12/03/2012',    1,    'Failure'UNION ALL
    SELECT '12/03/2012',    280,    'Success'
    
    
    SELECT 
    Dt,
    Success,
    Failure,
    Success + Failure AS Total
    FROM
    (SELECT * FROM @T )P
    PIVOT 
    (SUM(CNT) FOR [STATUS] IN (Failure,Success))PVT
    


    Thanks and regards, Rishabh K

    Monday, December 03, 2012 11:41 AM
  • Try this replace t_date with your date column and replace t_table with your table name:

    SELECT t_date AS DATE,
    count(CASE WHEN Status='Success' THEN 1 END) AS 'Success',
    count(CASE WHEN Status='Failure' THEN 1 END) AS 'Failure',
    count(1) AS 'Total'
    FROM t_table
    GROUP BY t_date


    Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

    Monday, December 03, 2012 12:42 PM
  • You should avoid key words as your object's name.

    SELECT Date, SUM(CASE WHEN Status='Success' THEN [count] END) AS 'Success',
    SUM(CASE WHEN Status='Failure' THEN [count] END) AS 'Failure',
    SUM(CASE WHEN Status='Failure' Or Status='Success' THEN [count] END) AS 'Total' FROM yourtable
    group by [Date]

    Monday, December 03, 2012 3:33 PM
    Moderator