CODEWITHSUNDEEP
sqlsql-servert-sql
Basil
Basil

Reputation: 1004

Dynamic pivot in SQL server: insert spaces in concatenation and referencing a pivot

I have a table in long format which I convert to wide format dynamically.

The code was heavily influenced by: SQL Server dynamic PIVOT query?

create table #temp
(
    ID int,
    category varchar(15),
    Answer varchar (5)
)

insert into #temp values ('1', 'breakfast','yes')
insert into #temp values ('1', 'lunch','no')
insert into #temp values ('1', 'dinner','yes')
insert into #temp values ('2', 'breakfast','no')
insert into #temp values ('2', 'lunch', 'yes')
insert into #temp values ('2', 'dinner', 'no')

select * from #temp

Which I can convert into wide format:

DECLARE @cols  AS VARCHAR(MAX)='';
DECLARE @query AS VARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) 


exec (
'SELECT ID, '+@cols+', concat('+@cols+' )as NewCol from 
(
    select ID, category,answer from #temp
) pivotexample
pivot 
(
    max(Answer) for category in (' + @cols + ')
) as pivotexample2'
) 

drop table #temp

The distinct values in the category column can change so I needed a dynamic solution (as above). This give the below pivoted output:

enter image description here

The issue I have is how can I insert a separator in the concatenation part that creates NewColumn in the pivot.

Also when I then run a select * from pivotexample2 query, it says Invalid object name 'pivotexample2'. I don't understand why this is, because this is the alias I have given it and want to reference it for things like joins further in the pipeline. How can I give it an alias so I can refence it again? Is it possible to put the pivot within a CTE so I can refence it again?

Upvotes: 0

Views: 236

Answers (1)

Carlos
Carlos

Reputation: 1822

You can use concat_ws:

DECLARE @cols  AS VARCHAR(MAX)='';
DECLARE @query AS VARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) 


exec (
'SELECT ID, '+@cols+', concat_ws('','', '+@cols+' )as NewCol from 
(
    select ID, category,answer from #temp
) pivotexample
pivot 
(
    max(Answer) for category in (' + @cols + ')
) as pivotexample2'
) 

drop table #temp

It would return:

ID breakfast dinner lunch NewCol
1 yes yes no yes,yes,no
2 no no yes no,no,yes

DBFiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=45dce502caf3b71662b963fb52dff94e

Upvotes: 1

Related Questions