T-SQL(联合国)数据透视表 [英] T-SQL (Un)Pivot Table
本文介绍了T-SQL(联合国)数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个如下视图(我认为这是访问更复杂的表会更容易的一个视图)
I have a view as follows (did a view as I thought it would be easier that accessing the more complicated table)
ID | aText1 | aText2 | aInt1 | aInt2
-------------------------------------
1 | ABC1 | XYZ1 | 2 | 20
2 | ABC1 | XYZ2 | 3 | 25
3 | ABC2 | XYZ2 | 1 | 30
4 | ABC2 | XYZ1 | 4 | 35
我需要读取结果
| XYZ1 | XYZ2
aText1 | aInt1 | aInt2 | aInt1 | aInt2
---------------------------------------
ABC1 | 2 | 20 | 3 | 25
ABC2 | 1 | 30 | 4 | 35
我尝试了各种方法,但都失败了. aText1
和aText2
可以是任意数量的值.尽可能接近该解决方案将非常有帮助
I've tried various pivots but all fail. aText1
and aText2
could be any number of values. As close to this solution as possible would be really helpful
推荐答案
我通常使用动态sql.像这样
I usually use dynamic sql. Something like this
create table #T
(
ID int,
aText1 varchar(4),
aText2 varchar(4),
aInt1 int,
aInt2 int
)
insert into #T
select 1, 'ABC1', 'XYZ1', 2, 20
union
select 2, 'ABC1', 'XYZ2', 3, 25
union
select 3, 'ABC2', 'XYZ2', 1, 30
union
select 4, 'ABC2', 'XYZ1', 4, 35
declare @sql nvarchar(max)
set @sql = 'select aText1 '
select @sql = @sql + ', SUM(case when aText2 = ''' + aText2 + ''' then aInt1 end) as [' + aText2 + ' - aInt1] '+
', SUM(case when aText2 = ''' + aText2 + ''' then aInt2 end) as [' + aText2 + ' - aInt2]'
from
(
select distinct aText2 from #T
) T
set @sql = @sql + ' from #T group by aText1'
exec sp_executeSQL @sql
drop table #T
或者您可以创建另一个视图(例如下一个示例中的#T2)并使用PIVOT
Or you can create another view (like #T2 in my next example) and use PIVOT
create table #T
(
ID int,
aText1 varchar(4),
aText2 varchar(4),
aInt1 int,
aInt2 int
)
insert into #T
select 1, 'ABC1', 'XYZ1', 2, 20
union
select 2, 'ABC1', 'XYZ2', 3, 25
union
select 3, 'ABC2', 'XYZ2', 1, 30
union
select 4, 'ABC2', 'XYZ1', 4, 35
create table #T2
(
aText1 varchar(4),
aText2 varchar(20),
aValue int
)
insert into #T2
select aText1, aText2 + ' - aInt1' as aText2, aInt1
from #T
union
select aText1, aText2 + ' - aInt2', aInt2
from #T
declare @sql nvarchar(max), @columns nvarchar(max)
set @columns = ''
select @columns = @columns + ', [' + aText2 + ']'
from (select distinct aText2 from #T2) as T
set @columns = substring(@columns, 2, len(@columns))
set @sql =
'
SELECT *
FROM
(SELECT
aText1,
aText2,
aValue
FROM
#T2
) AS SourceTable
PIVOT
(
SUM(aValue)
FOR aText2 in ('+@columns+')
) AS PivotTable'
exec sp_executeSQL @SQL
drop table #T2
drop table #T
这篇关于T-SQL(联合国)数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文