我有一个看起来像这样的表:
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 | mauriiciobarbosa@gmail.com 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 | mauriiciobarbosa@gmail.com | 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]
我找到了一些动态的枢轴示例,但无法将它们放入我的特定案例中。任何帮助,将不胜感激。谢谢。
编辑:
感谢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
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)
更新: 您缺少Alias名称sub-select
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)
演示:
模式设置
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', 'mauriiciobarbosa@gmail.com'), ('2016-09-24 14:30:11', 'mauricio', 'Texto Livre', 'nksnksjksj nsjsnsjjs'), ('2016-09-24 14:30:11', 'mauricio', 'Decimal', '0.9') ;
询问 :
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)
结果 :
╔═════════════════════════╦══════════╦════════════════════════════╦═════════╦══════════╦══════════════╦══════════════════════╗ ║ DtCreated ║ Decimal ║ Email ║ Inteiro ║ Senha ║ Telefone ║ Texto Livre ║ ╠═════════════════════════╬══════════╬════════════════════════════╬═════════╬══════════╬══════════════╬══════════════════════╣ ║ 2016-09-24 14:30:11.000 ║ mauricio ║ mauriiciobarbosa@gmail.com ║ 10 ║ 99658202 ║ (915) 438-05 ║ nksnksjksj nsjsnsjjs ║ ╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝