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!

Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Fabio, Jan 06 '21 at 04:55

score 1 · Accepted Answer · answered Jan 06 '21 at 04:45

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;

PSK · Answer 2 · 2021-01-06T05:03:55.793

In case if you don't know how many sites are there you need to do it using dynamic query like following.

--Generate column to be shown
DECLARE @cols AS NVARCHAR(MAX) = STUFF((SELECT DISTINCT ', ' + QUOTENAME(SITE) 
         FROM   #table
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
--Required when heading is different.
DECLARE @displayCols AS NVARCHAR(max) = 'ITEM , ' +  STUFF((SELECT DISTINCT ', CASE WHEN ' + QUOTENAME(SITE) 
                    + ' = 0 THEN ''No'' ELSE ''Yes'' END  AS ' + QUOTENAME( 'SITE_' + CAST(SITE AS VARCHAR(10))) 
         FROM   #table
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
--Pivot Query
DECLARE @query AS NVARCHAR(max) = ' SELECT ' + @displayCols + '
                                    FROM   #table
                                           PIVOT ( COUNT(SITE) 
                                                 FOR SITE IN ('+@cols+') ) pvt';
--Execute the Query
EXECUTE(@query)

Note : Replace #table with your table name in above query.

Working Demo

Output

|ITEM   |SITE_1 |SITE_2 |SITE_3 |SITE_4 |SITE_5| 
|A      |Yes    |Yes    |Yes    |No     |No    |
|B      |No     |Yes    |No     |Yes    |No    |
|C      |Yes    |No     |Yes    |Yes    |Yes   |

Dynamically convert vertical to horizontal data in SQL Server

2 Answers2