Pepper Pepper - 3 months ago 10
SQL Question

Pivot Table SQL Server

I need help with a pivot table on sql server.

I have a view that returns this result:

enter image description here

But the end user need some changes on the results, to be like this:

enter image description here

I made this query:

DECLARE @cols AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(pergunta)
from [dbo].[VRespostas]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @cols2 = '[NQuestionario],[Data],[Utilizador],' + @cols + ',[SubCategoria],[Observacoes]'

print @cols + ' '+ @cols2

set @query = 'SELECT ' + @cols2 + ' from
(
select *
from [dbo].[VRespostas]
) x
pivot
(
max(Resposta)
for Pergunta in (' + @cols + ')
) p '

execute(@query)


The result is almost what i want, but it gaves me two lines for any ID and i want one line only.

The result is:

enter image description here

What i've doing wrong?

Can u help me, please?

P.s.: sorry for my bad english. :)

Answer

NULL values in the result are due to the empty string in SubCategoria column.

Considering you have only one value in SubCategoria column other than empty string for a single NQuestionario

Instead of selecting SubCategoria values as it is, use max([SubCategoria])over(partition by [NQuestionario]) to replace the empty string with string value

DECLARE @cols AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(pergunta) 
                from [dbo].[VRespostas]
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
set @cols2 = '[NQuestionario],[Data],[Utilizador],' + @cols + ',[SubCategoria],[Observacoes]'

print @cols + '  '+ @cols2 

set @query = 'SELECT ' + @cols2 + ' from 
     (
        select [NQuestionario],[Data],[Utilizador],[Observacoes],pergunta
        ,max([SubCategoria])over(partition by [NQuestionario]) as [SubCategoria] --Here
        from [dbo].[VRespostas]
    ) x
    pivot 
    (
        max(Resposta)
        for Pergunta in (' + @cols + ')
    ) p '

execute(@query)