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. :)

Pரதீப் · Accepted Answer · 2016-08-26 17:35:54Z

1

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)

answered Aug 26, 2016 at 17:35

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Serg Over a year ago

Using plain conditional aggregation .. ((SELECT distinct ', max(case pergunta when ''' + pergunta + ''' then pergunta end) as ' + QUOTENAME(pergunta) ... instead of pivot all aggregations could be consistently done in a single step

Pரதீப் Over a year ago

@Serg - There is nothing wrong dynamic column list generation. Did you read my answer

Serg Over a year ago

Too long to comment, I'll post my version.

Pepper Over a year ago

Works fine!! Thanks a lot for your help!!

Serg · Accepted Answer · 2016-08-29 10:35:49Z

0

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)

edited Aug 29, 2016 at 10:35

answered Aug 26, 2016 at 18:28

Serg

22.9k5 gold badges23 silver badges50 bronze badges

2 Comments

Pepper Over a year ago

I've need to make a groupby clause, but still not working as it must. The header is fine, but the rows was equal from header.

Serg Over a year ago

group by is added.

Collectives™ on Stack Overflow

Pivot Table SQL Server

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related