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

小编典典

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

sql

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

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)

阅读 183

2021-05-16

共1个答案

小编典典

这是使用的一种方法 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

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 ║
╚═════════════════════════╩══════════╩════════════════════════════╩═════════╩══════════╩══════════════╩══════════════════════╝

2021-05-16