SQL Rows to Dynamics Columns

Question

I would like to convert some rows that is being extracted in my sql query into columns. Not all rows are duplicated in the result. So would like to know how can I convert rows to columns and if no specific value in the specific column, I would like to see blank value.

select Dep.ID,
       Dep.Department,
       L.Logo,
       ISNULL(R.Room,'') AS [Room],
  from vDepartment Dep
       left join vLogos L on L.ID=Dep.ID
       left join Room R on R.id=Dep.id

Current Data and expected result:

I think you must use group_concat (in mysql) equivalent in sql-server (I think it must be STRING_AGG but after sql-server 2017 https://database.guide/the-sql-server-equivalent-to-group_concat/), not creating new columns. — Mostafa Vatanpour, Sep 05 '19 at 00:39
Hi Mostafa, what if I want to have them in different columns - like the expected result shown above? Is there a work around as I do not want them to be separated by a wildcard character. — Karu3103, Sep 05 '19 at 00:50

score 1 · Accepted Answer · answered Sep 05 '19 at 00:54

Here's your script. First is to generate dynamic columns, then build sql query string to include your generic columns. And use sql pivot()

declare  @cols nvarchar(max);
declare  @sql nvarchar(1000);
//generate Room columns
select @cols =
STUFF((select N'],[' + room
       from (select distinct Room
          from Room ) AS t1   
       FOR XML PATH('')
      ), 1, 2, '') + N']';

set @sql = N'select p.ID, p.Department, p.Logo, ' + @cols + N' from 
             (
                select Dep.ID,
                   Dep.Department,
                   L.Logo,
                   ISNULL(R.Room,'') AS [Room],
                from vDepartment Dep
                   left join vLogos L on L.ID=Dep.ID
                   left join Room R on R.id=Dep.id 
            ) t1
            pivot 
            (
                max(t1.Room)
                for t1.Room in (' + @cols + N')
            ) p '

exec sp_executesql @sql;

SQL Rows to Dynamics Columns

1 Answers1