id item value
1 event Fee 10
1 reg Fee 50
2 event Fee 20
2 reg Fee 30
3 reg Fee 15
对于这样的表,要进行行列转换是很容易的,可以用下面的语句轻松地转换:
select * from (
select 1 as id,'event Fee' as item,10 as value union all
select 1 as id,'reg Fee' as item,50 as value union all
select 2 as id,'event Fee' as item,20 as value union all
select 2 as id,'reg Fee' as item,30 as value union all
select 3 as id,'reg Fee' as item,15 as value
)tb
pivot (max(value) for item in ([event Fee],[reg Fee]))pvt
转换结果如下:
id event Fee reg Fee
1 10 50
2 20 30
3 NULL 15但现在原表添加了两列
id item qty price value
1 event Fee 1 20 10
1 reg Fee 2 5 50
2 event Fee 5 10 20
2 reg Fee 6 8 30
3 reg Fee 5 10 50
要转换为类似下面的形式,该怎么办呢?
id qty price value qty price value
1 1 20 10 2 5 50
2 5 10 20 6 8 30
3 NULL NULL NULL 5 10 50
左边的qty,price,value是event Fee的,右边是reg Fee的,当然新表的列名可以改
请问大家,该怎么写?select * from (
select 1 as id,'event Fee' as item,1 as qty,20 as price,10 as value union all
select 1 as id,'reg Fee' as item,2 as qty,5 as price,50 as value union all
select 2 as id,'event Fee' as item,5 as qty,10 as price,20 as value union all
select 2 as id,'reg Fee' as item,6 as qty,8 as price,30 as value union all
select 3 as id,'reg Fee' as item,5 as qty,10 as price,50 as value
)tb
pivot 。。????
1 event Fee 10
1 reg Fee 50
2 event Fee 20
2 reg Fee 30
3 reg Fee 15
对于这样的表,要进行行列转换是很容易的,可以用下面的语句轻松地转换:
select * from (
select 1 as id,'event Fee' as item,10 as value union all
select 1 as id,'reg Fee' as item,50 as value union all
select 2 as id,'event Fee' as item,20 as value union all
select 2 as id,'reg Fee' as item,30 as value union all
select 3 as id,'reg Fee' as item,15 as value
)tb
pivot (max(value) for item in ([event Fee],[reg Fee]))pvt
转换结果如下:
id event Fee reg Fee
1 10 50
2 20 30
3 NULL 15但现在原表添加了两列
id item qty price value
1 event Fee 1 20 10
1 reg Fee 2 5 50
2 event Fee 5 10 20
2 reg Fee 6 8 30
3 reg Fee 5 10 50
要转换为类似下面的形式,该怎么办呢?
id qty price value qty price value
1 1 20 10 2 5 50
2 5 10 20 6 8 30
3 NULL NULL NULL 5 10 50
左边的qty,price,value是event Fee的,右边是reg Fee的,当然新表的列名可以改
请问大家,该怎么写?select * from (
select 1 as id,'event Fee' as item,1 as qty,20 as price,10 as value union all
select 1 as id,'reg Fee' as item,2 as qty,5 as price,50 as value union all
select 2 as id,'event Fee' as item,5 as qty,10 as price,20 as value union all
select 2 as id,'reg Fee' as item,6 as qty,8 as price,30 as value union all
select 3 as id,'reg Fee' as item,5 as qty,10 as price,50 as value
)tb
pivot 。。????
sum(case when item='event Fee' then qty else 0 end) as qty,
sum(case when item='event Fee' then price else 0 end) as price,
sum(case when item='event Fee' then value else 0 end) as value,
sum(case when item='reg Fee' then qty else 0 end) as qty1,
sum(case when item='reg Fee' then price else 0 end) as price1,
sum(case when item='reg Fee' then value else 0 end) as value1
from tb
group by id
declare @sql varchar(max)set @sql = 'select id,'
select @sql = @sql + ',max(case item when ''' + item + ''' then qty else 0 end) as qty[' + item + ']'
+ ',max(case item when ''' + item + ''' then price else 0 end) as qty[' + item + ']'
+ ',max(case item when ''' + item + ''' then [value] else 0 end) as qty[' + item + ']'
from (select distinct item from tb)u
select @sql = @sql + ' from tb group by id'
exec(@sql)