Pivot Table SQL Server

Question

I need help with a pivot table on sql server.

I have a view that returns this result:

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

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:

What i've doing wrong?

Can u help me, please?

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

Answer 1

Using plain conditional aggregation to do all aggregations consistently in a single step.

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

select @cols = (
   select  distinct ', max(case pergunta when ''' + pergunta + ''' then pergunta end) as ' + QUOTENAME(pergunta) 
   from [dbo].[VRespostas]
   FOR XML PATH('') ) 

set @query = 'SELECT [NQuestionario],[Data],[Utilizador]' + @cols + ' max([SubCategoria]) as [SubCategoria], max([Observacoes]) as [Observacoes]  
 from [dbo].[VRespostas]
 group by [NQuestionario],[Data],[Utilizador]'


execute(@query)

Answer 2

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)