使用动态SQL语句进行数据透视 [英] Pivot using Dynamic SQL statement
问题描述
这是数据的一小部分.在实际数据中,名称"和代码"下的值有数百个,并且这些值经常更改. 因此,对Pivot语句进行硬编码将不起作用.需要创建一个动态SQL语句-我需要帮助.
This is a small sample of the data. In the actual data, the values under Name and Code are in the hundreds and those values change frequently. For this reason, hard-coding the Pivot statement will not work. There needs to be a dynamic SQL statement created - and I need help with that.
DECLARE @Test Table
(
Name Varchar(32),
Code Varchar(20)
)
INSERT INTO @Test(Name, Code) VALUES
('A-1', 'A-One')
, ('A 2', 'A-Two')
, ('B 1-b', 'B-One')
, ('B', 'A-Two')
, ('C', 'A-One')
, ('C', 'B-One')
, ('C', 'C-One')
样本数据集看起来像这样[同样,这只是一个小样本]:
The sample data set looks like this [again, this is just a small sample]:
Name Code
A-1 A-One
A 1 A-Two
B 1-b B-One
B A-Two
C A-One
C B-One
C C-One
请注意,代码"值(例如A-One,A-Two和B-One)可能与一个以上的Name值相关联.
Notice that Code values [like A-One, A-Two, and B-One] may be associated with more than one Name value.
例如A-One出现,名称为A-1,以及名称C ...
E.g. A-One appears with Name A-1, as well as Name C ...
我想输出它,使它看起来像这样[除了,比我显示的值多得多-这些值可以更改]:
I want to output it so it looks like this [except, with a lot more values than I am showing - and those values can change]:
A-1 A 1 B 1-b B C
A-One X X
A-Two X X
B-One X X
C-One X
名称"值和代码"值的数量可以更改.它们不是恒定的.
The number of 'Name' values and Code values can change. They are not constant.
目标是能够查看左侧的Code值列表-并轻松查看与Codes关联的Name值.
The goal is to be able to look down the list of Code values on the left - and easily see which Name values the Codes are associated with.
我相信这需要创建动态透视图sql,并且在理解Pivot sql时遇到问题,我将不胜感激任何帮助或指针.
I believe this requires dynamic pivot sql to be created and I have trouble understanding Pivot sql and I would appreciate any help or pointers.
推荐答案
您可以使用动态数据透视表:
You can use a dynamic pivot:
create table dbo.test([Name] Varchar(32), Code Varchar(30))
insert into dbo.test values
('A-1', 'A-One')
, ('A 2', 'A-Two')
, ('B 1-b', 'B-One')
, ('B', 'A-Two')
, ('C', 'A-One')
, ('C', 'B-One')
, ('C', 'C-One')
declare @cols nvarchar(max)='' --holds all the values that will become column names
declare @alias nvarchar(max)='' --holds values that will become column names and converts values to 'x'
declare @sql nvarchar(max)='' --contains the TSQL dinamically generated
select @cols = @cols + ', [' + [Name] + ']'
from dbo.test
group by [Name]
select @alias = @alias + ', case when T.[' + [Name] + '] is not null then ''x'' else '''' end as [' + [Name] + ']'
from dbo.test
group by [Name]
set @cols = RIGHT(@cols, len(@cols)-2)
set @sql = @sql + ' select T.Code ' + @alias + ' from ('
set @sql = @sql + ' select piv.[Code], ' + @cols
set @sql = @sql + ' from dbo.test '
set @sql = @sql + ' pivot (max([Name]) for [Name] in ('+@cols+') ) piv '
set @sql = @sql + ' ) T'
--execute dynamic query
exec(@sql)
结果:
这篇关于使用动态SQL语句进行数据透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!