0

I have a normalized table that I'd like to 'Flatten' dynamically.

Picture below for example input and output. The columns need to be fetched dynamically, it can't be hard coded. IO

So far, this is what I'm working on.

`DECLARE @Query nvarchar(MAX) = '';`

DECLARE @columns nvarchar(1000) = '';`

Select @cols = @cols + QUOTENAME(Food) + ',' FROM (select distinct Food from tblPeopleFoods ) as tmp

Select @cols = substring(@cols, 0, len(@cols))

At this point, I have the properly comma separated string for my column names.

I'm hung up on the PIVOT query to flag each person 1 or 0 for having each food.

This is what I have so far

set @query =

'SELECT * from

(

    select Person, Food from tblPeopleFoods

) src

pivot

(

  count(*)   for Person in (' + @cols + ')

) piv
Scotch
  • 3,186
  • 11
  • 35
  • 50
  • 2
    This has been asked and answered dozens and dozens of times. – Sean Lange Oct 30 '19 at 21:25
  • [[sql-server\] Dynamic Pivot](https://stackoverflow.com/search?q=%5Bsql-server%5D+dynamic+pivot). Pick and choose your answer from the results – Thom A Oct 30 '19 at 21:27
  • @SeanLange In the examples I've seen, they are pivoting values, not flagging whether it exists – Scotch Oct 30 '19 at 21:27
  • 1
    If there's a count of 1 (or more) it exists. That's still a Pivot. – Thom A Oct 30 '19 at 21:28
  • I figured it out. I just needed to add derive a column in the source of the pivot, just a '1' , and then just count/sum that field in the pivot clause – Scotch Oct 30 '19 at 21:30
  • Yeah same concept, just need to add an aggregate. Glad you got it sorted. – Sean Lange Oct 30 '19 at 21:31

0 Answers0