Reformatting of SQL Data for chart

Question

I need help with formatting the data from a sql query for creating charts. This is the query below.

SELECT install_status, Date_appended , COUNT(serial_number)
FROM Asset_Base
Where  (Date_appended = '2017-09-26' OR Date_appended = '2017-08-31' OR Date_appended = '2017-07-31')
AND (install_status= 'Installed' 
OR (install_status= 'In Stock' AND u_install_sub_status = 'Available')
OR install_status= 'Missing') 
Group By install_status , Date_appended

Which returns :

install_status  Date_appended           (No column name)
In Stock        2017-08-31 00:00:00.000 10981
Installed       2017-08-31 00:00:00.000 58764
Missing         2017-08-31 00:00:00.000 51661
In Stock        2017-09-26 00:00:00.000 10830
Installed       2017-09-26 00:00:00.000 59083
Missing         2017-09-26 00:00:00.000 51209

Is there any way I can get the data to look like this instead?

Date_appended In Stock Installed Missing
2017-08-31    10981    58764     51661
2017-09-26    10830    59083     51209

Thanks!

There is, have a read of this one to do with Pivoting... https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Alan, Oct 02 '17 at 07:13

score 0 · Answer 1 · answered Oct 02 '17 at 07:59

If you just want one row per Date_appended just group by this:

  ...
GROUP BY appended;

If you want to have a column only counting serial_numbers where install_status is In Stock use the following:

SELECT
 COUNT(CASE WHEN install_status = 'In Stock' THEN 1 END),
 ...

The case expressions maps every non In Stock row to null (due to the implied else null clause of case). count generally ignores null values (like most aggregate functions).

The other columns can be added likewise.

Read more about this pivot technique here: http://modern-sql.com/use-case/pivot

score 0 · Answer 2 · answered Oct 02 '17 at 08:49

I think that you may use pivot. Hoping the below query is helpful for you :

       SELECT * FROM (
        select 'In Stock' as install_status, '2017-08-31' as Date_appended  , 10981 as 'serial_number'
        UNION ALL
        select 'Installed' as install_status, '2017-08-31' as Date_appended  , 58764 as 'serial_number'
        UNION ALL
        select 'Missing' as install_status, '2017-08-31' as Date_appended  , 51661 as 'serial_number'
        ) AS A
        PIVOT
        (
            MAX(serial_number)
            For install_status in ([In Stock] ,[Installed],[Missing])
        ) PT

RESULT : 
    Date_appended In Stock    Installed   Missing
    ------------- ----------- ----------- -----------
    2017-08-31    10981       58764       51661

    (1 row(s) affected)

score 0 · Answer 3 · answered Oct 02 '17 at 10:20

You can use this query.

SELECT * FROM 
    ( SELECT install_status, serial_number , Date_appended FROM @Asset_Base
      WHERE Date_appended IN ('2017-09-26', '2017-08-31', '2017-07-31')
            AND (install_status IN ( 'Installed' , 'Missing')
                OR (install_status= 'In Stock' AND u_install_sub_status = 'Available')) ) SRC 
        PIVOT ( COUNT(serial_number) FOR install_status IN ([In Stock], [Installed], [Missing])) PVT

Reformatting of SQL Data for chart

3 Answers3