Cast unique column values into separate "flag" columns

Question

With table "Groceries" having columns "Name" and "Item", with the format:

*Name* | *Item*
 Bill  |  Eggs
 Bill  |  Milk
 Sam   |  Eggs
 Sam   |  Turkey
 Sam   |  Butter
 Josh  |  Milk
 Josh  |  Butter

I'm looking to generalize a query to cast these "Items" out into their own columns, acting as flags.

My current query is essentially:

SELECT
   MAX(CASE WHEN grocery.Item = 'Eggs' THEN 1 ELSE 0 END) 'Eggs',
   MAX(CASE WHEN grocery.Item = 'Milk' THEN 1 ELSE 0 END) 'Milk',
   MAX(CASE WHEN grocery.Item = 'Turkey' THEN 1 ELSE 0 END) 'Turkey',
   MAX(CASE WHEN grocery.Item = 'Butter' THEN 1 ELSE 0 END) 'Butter'
FROM
   Groceries grocery

which works, but I want to generalize it somehow so that rather than listing out each unique value and using this "MAX(CASE WHEN" logic on each one, the query does this automatically for whichever distinct values are in the grocery.Item column.

Is there any way to do this generalization, or will I simply have to list out each value? Thanks in advance.

Your looking for a Dynamic Pivot https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — John Cappelletti, Jun 21 '19 at 14:05
Just for fun, here is a little nudge https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0803469af4e3ce4a2f20009d8c3a8c03 — John Cappelletti, Jun 21 '19 at 14:14
@JohnCappelletti still pretty new to SQL so I'm going to have to dig into these - thanks for the help, much appreciated! — mizichael, Jun 21 '19 at 15:35

score 0 · Answer 1 · answered Jun 21 '19 at 14:15

First of all create the select clause:

DECLARE @SELECT VARCHAR(MAX)=''

SELECT @SELECT = @SELECT + ',MAX(CASE WHEN grocery.Item =''' + ITEM + ''' THEN 1 ELSE 0 END)  ['+ITEM+']'+CHAR(13) FROM (SELECT DISTINCT ITEM FROM Groceries) grocery
SELECT @SELECT = STUFF(@SELECT,1,1,'')

then create the query and execute it:

DECLARE @QUERY NVARCHAR(MAX)='
    SELECT '+@SELECT+' FROM Groceries grocery
'

EXEC sp_executesql @STMT = @QUERY

Cast unique column values into separate "flag" columns

1 Answers1