sql - 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:

标签： sqlsql-serverpivot

Answer 1

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;