Pivot using Dynamic SQL statement

Question

This is a small sample of the data. In the actual data, the values under Name and Code are in the hundreds and those values change frequently. For this reason, hard-coding the Pivot statement will not work. There needs to be a dynamic SQL statement created - and I need help with that.

DECLARE @Test Table
(
  Name   Varchar(32),
  Code   Varchar(20)
)

INSERT INTO @Test(Name, Code) VALUES
  ('A-1', 'A-One')
, ('A 2', 'A-Two')
, ('B 1-b', 'B-One')
, ('B', 'A-Two')
, ('C', 'A-One')
, ('C', 'B-One')
, ('C', 'C-One')

The sample data set looks like this [again, this is just a small sample]:

Name    Code
A-1     A-One
A 1     A-Two
B 1-b   B-One
B       A-Two 
C       A-One
C       B-One
C       C-One

Notice that Code values [like A-One, A-Two, and B-One] may be associated with more than one Name value.

E.g. A-One appears with Name A-1, as well as Name C ...

I want to output it so it looks like this [except, with a lot more values than I am showing - and those values can change]:

             A-1      A 1        B 1-b          B      C
A-One        X                                         X
A-Two                  X                        X   
B-One                             X                    X
C-One                                                  X

The number of 'Name' values and Code values can change. They are not constant.

The goal is to be able to look down the list of Code values on the left - and easily see which Name values the Codes are associated with.

I believe this requires dynamic pivot sql to be created and I have trouble understanding Pivot sql and I would appreciate any help or pointers.

Answer 1

You can use a dynamic pivot:

create table dbo.test([Name] Varchar(32), Code Varchar(30)) 

insert into dbo.test values 
  ('A-1', 'A-One')
, ('A 2', 'A-Two')
, ('B 1-b', 'B-One')
, ('B', 'A-Two')
, ('C', 'A-One')
, ('C', 'B-One')
, ('C', 'C-One')

declare @cols nvarchar(max)=''  --holds all the values that will become column names 
declare @alias nvarchar(max)='' --holds values that will become column names and converts values to 'x'
declare @sql nvarchar(max)=''   --contains the TSQL dinamically generated 

select @cols = @cols + ', [' + [Name] + ']' 
from dbo.test 
group by [Name] 

select @alias = @alias + ', case when T.[' + [Name] + '] is not null then ''x'' else '''' end as [' + [Name] + ']'
from dbo.test 
group by [Name] 

set @cols = RIGHT(@cols, len(@cols)-2) 
set @sql = @sql + ' select  T.Code ' + @alias + ' from ('
set @sql = @sql + ' select piv.[Code], ' + @cols 
set @sql = @sql + ' from dbo.test ' 
set @sql = @sql + ' pivot (max([Name]) for [Name] in ('+@cols+') ) piv ' 
set @sql = @sql + ' ) T' 

--execute dynamic query
exec(@sql)

Results: