Dynamically "pivot" rows into columns - SQL Server

Question

I have a table that looks like this:

DtCreated               | UserName | Question    | Answer
2016-09-24 14:30:11.927 | mauricio | Senha       | 99658202
2016-09-24 14:30:11.927 | mauricio | Inteiro     | 10
2016-09-24 14:30:11.927 | mauricio | Telefone    | (915) 438-05
2016-09-24 14:30:11.927 | mauricio | Email       | [email protected]
2016-09-24 14:30:11.927 | mauricio | Texto Livre | nksnksjksj nsjsnsjjs
2016-09-24 14:30:11.927 | mauricio | Decimal     | 0.9

How can I use pivot to transform this data into this?

DtCreated               | UserName | Senha    | Inteiro | Telefone     | Email                      | Texto Livre          | Decimal
2016-09-24 14:30:11.927 | mauricio | 99658202 |  10     | (915) 438-05 | [email protected] | nksnksjksj nsjsnsjjs | 0.9

PS: "Question" column has dynamically created values and data should be grouped by "DtCreated" and "Username" fields.

This is the SQL code i'm using right now:

SELECT 

sf.[DtCreated],    
sf.[UserName],
fc.Title as Question,
sv.Value as Answer

  FROM [form].[SubmissionForm] sf
  inner join [form].[Submission] s on
  sf.id = s.SubmissionForm_Id
  inner join [form].[FormComponent] fc on s.FormComponentId = fc.Id
  inner join [form].[SubmissionValue] sv on s.Id = sv.Submission_Id
  where sf.Id = '0DBA8590-C33E-48F8-9E64-C68EEFC536FF'
  order by sf.[DtCreated]

I've found some dynamic pivot examples but could not fit them into my specific case. Any help would be appreciated. Thanks.

EDIT:

Thanks to Prdp, I'm almost there. This is the dynamic pivot code 'til now:

DECLARE @sql      VARCHAR(max)='', 
        @col_list VARCHAR(8000)='' 

SET @col_list = (SELECT DISTINCT Quotename(fc.Title) + ',' 
                 FROM   [form].[SubmissionForm] sf
  inner join [form].[Submission] s on
  sf.id = s.SubmissionForm_Id
  inner join [form].[FormComponent] fc on s.FormComponentId = fc.Id
  inner join [form].[SubmissionValue] sv on s.Id = sv.Submission_Id
                 FOR xml path('')) 

SET @col_list = LEFT (@col_list, Len(@col_list) - 1) 

SET @sql = 'select [DtCreated],[UserName]' + @col_list 
           + ' from (SELECT 

sf.[DtCreated],    
sf.[UserName],
fc.Title,
sv.Value

  FROM [form].[SubmissionForm] sf
  inner join [form].[Submission] s on
  sf.id = s.SubmissionForm_Id
  inner join [form].[FormComponent] fc on s.FormComponentId = fc.Id
  inner join [form].[SubmissionValue] sv on s.Id = sv.Submission_Id
) pivot (max([sv.Value]) for [fc.Title] in (' 
           + @col_list + '))pv' 

EXEC(@sql)

You can use a dynamic pivot or a dynamic cross tab for this sort of thing. This has been answered thousands of times on SO and other places around the internet. — Sean Lange
– Sean Lange, Commented Oct 5, 2016 at 16:57
I'm having trouble to transform this sql code. Can you show me some sample code, please? Thanks. — thiago
– thiago, Commented Oct 5, 2016 at 17:00
Save the result of your query to temp table and apply one of the dynamic pivot solutions already posted on SO. — Serg
– Serg, Commented Oct 5, 2016 at 17:05

Pரதீப் · Accepted Answer · 2016-10-05 17:42:07Z

2

Here is one way to do it using Dynamic Pivot

DECLARE @sql      VARCHAR(max)='', 
        @col_list VARCHAR(8000)='' 

SET @col_list = (SELECT DISTINCT Quotename([question]) + ',' 
                 FROM   Yourquery
                 FOR xml path('')) 

SET @col_list = LEFT (@col_list, Len(@col_list) - 1) 

SET @sql = 'select [DtCreated],[UserName]' + @col_list 
           + ' from Yourquery pivot (max([Answer]) for [Question] in (' 
           + @col_list + '))pv' 

EXEC(@sql)

Update : You are missing Alias name to the sub-select

SET @sql = 'select [DtCreated],[UserName]' + @col_list 
           + ' from (SELECT 

sf.[DtCreated],    
sf.[UserName],
fc.Title,
sv.Value

  FROM [form].[SubmissionForm] sf
  inner join [form].[Submission] s on
  sf.id = s.SubmissionForm_Id
  inner join [form].[FormComponent] fc on s.FormComponentId = fc.Id
  inner join [form].[SubmissionValue] sv on s.Id = sv.Submission_Id
) a --here
pivot (max([sv.Value]) for [fc.Title] in (' 
           + @col_list + '))pv' 

EXEC(@sql)

DEMO :

Schema setup

CREATE TABLE #Table1
    ([DtCreated] datetime, [UserName] varchar(8), [Question] varchar(11), [Answer] varchar(26))
;


INSERT INTO #Table1
    ([DtCreated], [UserName], [Question], [Answer])
VALUES
    ('2016-09-24 14:30:11', 'mauricio', 'Senha', '99658202'),
    ('2016-09-24 14:30:11', 'mauricio', 'Inteiro', '10'),
    ('2016-09-24 14:30:11', 'mauricio', 'Telefone', '(915) 438-05'),
    ('2016-09-24 14:30:11', 'mauricio', 'Email', '[email protected]'),
    ('2016-09-24 14:30:11', 'mauricio', 'Texto Livre', 'nksnksjksj nsjsnsjjs'),
    ('2016-09-24 14:30:11', 'mauricio', 'Decimal', '0.9')
;

Query :

declare @sql varchar(max)='',@col_list varchar(8000)=''

set @col_list = (select distinct quotename([Question])+',' from #Table1
for xml path(''))

set @col_list = left (@col_list,len(@col_list)-1)

set @sql = 'select [DtCreated],[UserName]'+@col_list+' from
#Table1
pivot (max([Answer]) for [Question] in ('+@col_list+'))pv'

exec(@sql)

Result :

╔═════════════════════════╦══════════╦════════════════════════════╦═════════╦══════════╦══════════════╦══════════════════════╗
║        DtCreated        ║ Decimal  ║           Email            ║ Inteiro ║  Senha   ║   Telefone   ║     Texto Livre      ║
╠═════════════════════════╬══════════╬════════════════════════════╬═════════╬══════════╬══════════════╬══════════════════════╣
║ 2016-09-24 14:30:11.000 ║ mauricio ║ [email protected] ║      10 ║ 99658202 ║ (915) 438-05 ║ nksnksjksj nsjsnsjjs ║
╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝

edited Oct 5, 2016 at 17:42

answered Oct 5, 2016 at 17:01

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

5 Comments

thiago Over a year ago

I've removed where clause and order by for testing purposes, This is what I got: Mensagem 1038, Nível 15, Estado 4, Linha 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. Mensagem 156, Nível 15, Estado 1, Linha 13 Incorrect syntax near the keyword 'pivot'. Mensagem 1038, Nível 15, Estado 4, Linha 13

Pரதீப் Over a year ago

@thiago - post your dynamic query in question

thiago Over a year ago

Did not work: Mensagem 1038, Nível 15, Estado 4, Linha 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. Mensagem 102, Nível 15, Estado 1, Linha 13 Incorrect syntax near 'a'. Mensagem 1038, Nível 15, Estado 4, Linha 14 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] a....

Pரதீப் Over a year ago

@thiago - Are you sure the error message is generated from dynamic pivot query. I have added demo also for me it is working

thiago Over a year ago

I put my query into a temp table and process pivot from it. Now it's working fine! Thank you very much Prdp! You're the man!!

Collectives™ on Stack Overflow

Dynamically "pivot" rows into columns - SQL Server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related