Dynamically convert vertical to horizontal data in SQL Server

Question

Currently I'm trying to convert a vertical data into horizontal data. I believe it can be done with Java but wonder if it can be done instead using just SQL query which then can save me a lot of time.

The number of site column is a fixed value.

The data is like this:

ITEM   SITE
A      1
A      2
A      3
B      2
B      4
C      1
C      3
C      4
C      5
...

It then need to be converted to be like this:

ITEM   SITE_1   SITE_2   SITE_3   SITE_4   SITE_5   SITE_6   SITE_7
A      YES      YES      YES      NO       NO       NO       NO
B      NO       YES      NO       YES      NO       NO       NO
C      YES      NO       YES      YES      YES      NO       NO
...

Thank you all very much!

Answer 1

If number of site values are fixed then you can use the conditional aggregation as follows:

select item,
       max(case when site = 1 then 'YES' else 'NO' end) as site_1,
       max(case when site = 2 then 'YES' else 'NO' end) as site_2,
       max(case when site = 3 then 'YES' else 'NO' end) as site_3,
       max(case when site = 4 then 'YES' else 'NO' end) as site_4,
       max(case when site = 5 then 'YES' else 'NO' end) as site_5,
       max(case when site = 6 then 'YES' else 'NO' end) as site_6,
       max(case when site = 7 then 'YES' else 'NO' end) as site_7
  from your_table
group by item;