动态地将行“透视”为列 - SQL Server

我有一个看起来像这样的表：

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] /cdn-cgi/l/email-protection
2016-09-24 14:30:11.927 | mauricio | Texto Livre | nksnksjksj nsjsnsjjs
2016-09-24 14:30:11.927 | mauricio | Decimal     | 0.9

我如何使用数据透视表将这些数据转换成这样？

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

PS：“问题”列具有动态创建的值，数据应按“DtCreated”和“用户名”字段分组。

这是我现在使用的 SQL 代码：

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]

我找到了一些动态枢轴示例，但无法将它们适合我的具体情况。 任何帮助，将不胜感激。 谢谢。

EDIT:

感谢 Prdp，我就快到了。这是到目前为止的动态数据透视代码：

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) 

这是一种使用方法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 :你失踪了Alias名字给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 :

架构设置

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] /cdn-cgi/l/email-protection'),
    ('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 ║ mau[email protected] /cdn-cgi/l/email-protection ║      10 ║ 99658202 ║ (915) 438-05 ║ nksnksjksj nsjsnsjjs ║
╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝