Using 2 groups, is it possible to have the 2nd group broken out as columns?

Question

I have a query that is similar to this:

SELECT dateadd(DAY,0, datediff(day,0, archivedate)) as day, 
    COUNT(*) item_value,
    item_metadata.item_label
FROM item_values
join item_metadata on item_values.md_ID= item_metadata.Id
group by dateadd(DAY,0, datediff(day,0, archivedate)), item_label
order by dateadd(DAY,0, datediff(day,0, archivedate)) desc

This results in data that looks like this:

day       item_value    item_label
2020-03-31  8           orange
2020-03-31  2           black
2020-03-30  28          green
2020-03-30  1           blue
2020-03-30  59          orange
2020-03-29  11          black
2020-03-29  1           blue
2020-03-29  15          green
2020-03-29  4           orange

I want to have it look like this though:

day         orange   black   green   blue
2020-03-31  8        2       0       0
2020-03-30  59       0       28      1
2020-03-29  4        11      15      1

Is this possible in SQL Server?

I'm basically wanting to eventually visualize the data as a stacked chart.

SOLVED Based on the other links posted below the solution i found was:

 declare
    @cols nvarchar(max),
    @stmt nvarchar(max)

select @cols = isnull(@cols + ', ', '') + '[' + T.item_label + ']' from (select distinct item_label from item_metadata where Active=1) as T

select @stmt = '
select * from (SELECT dateadd(DAY,0, datediff(day,0, archivedate)) as [day], 
COUNT(*) as item_value,
item_label
  FROM item_values
  join item_metadata on item_values.md_ID= item_metadata.Id
   where Ignored=0 group by dateadd(DAY,0, datediff(day,0, archivedate)),item_label ) Visits
    pivot(sum(item_value) for item_metadata
    in (' + @cols + ') ) as pivottable order by day desc
'
exec sp_executesql  @stmt = @stmt

You want to `PIVOT` the data. Check this example. --> https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server/15931734 — Digvijay S, Mar 31 '20 at 05:34

score 1 · Accepted Answer · edited Mar 31 '20 at 06:12

Demo on db<>fiddle

You might be looking for Dynamic Pivot query


DECLARE 
    @columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';


SELECT @columns += QUOTENAME(item_label) + ','
From (SELECT DISTINCT item_label 
      FROM #a) A

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);


SET @sql = 'SELECT * 
            FROM ( SELECT day,item_value,item_label 
                   FROM  #a) src 
                   PIVOT( MAX([item_value]) FOR item_label IN ('+ @columns +')) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Output

yep this is exactly what i needed – omniron Mar 31 '20 at 06:21 — omniron, Mar 31 '20 at 06:21

Using 2 groups, is it possible to have the 2nd group broken out as columns?

1 Answers1