关于类似的行列转换的疑难问题
举例数据如图所示
转成如下图所示
行列转换 SQLSERVER
[解决办法]
动态的
--create table t(商品 varchar(100),
--价格 int,
--商店 varchar(100),
--销量 int)
--insert into t
--values('商品1',15,'商店1',3)
--insert into t
--values('商品1',15,'商店2',5)
--insert into t
--values('商品1',15,'商店3',6)
--insert into t
--values('商品2',15,'商店1',4)
--insert into t
--values('商品2',15,'商店2',6)
--insert into t
--values('商品2',15,'商店3',5)
--insert into t
--values('商品3',15,'商店1',7)
--insert into t
--values('商品3',15,'商店2',5)
--insert into t
--values('商品3',15,'商店3',6)
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('销量')+'=max(case when [商店]='+quotename(商店,'''')+' then [销量] else 0 end)'
+','+quotename(商店)+'=max(case when [商店]='+quotename(商店,'''')+' then [商店] else ''0'' end)'
from t group by 商店
exec('select [商品],价格'+@s+' from t group by [商品],价格')
/*
商品 价格 销量 商店1 销量 商店2 销量 商店3
---------------------------------------------------------------------------------------------------- ----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
商品1 15 3 商店1 5 商店2 6 商店3
商品2 15 4 商店1 6 商店2 5 商店3
商品3 15 7 商店1 5 商店2 6 商店3
*/
create table #tb(商品 varchar(100), 价格 int, 商店 varchar(100), 销量 int)
insert into #tb
values('商品1',15,'商店1',3)
insert into #tb
values('商品1',15,'商店2',5)
insert into #tb
values('商品1',15,'商店3',6)
insert into #tb
values('商品2',10,'商店1',4)
insert into #tb
values('商品2',10,'商店2',6)
insert into #tb
values('商品2',10,'商店3',5)
insert into #tb
values('商品3',13,'商店1',7)
insert into #tb
values('商品3',13,'商店2',5)
insert into #tb
values('商品3',13,'商店3',6)
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(商店)+']=max(case 商店 when '''+rtrim(商店)+''' then 商店 end),[销量]=sum(case 商店 when '''+rtrim(商店)+''' then 销量 end)'
from #tb group by 商店
exec('select 商品,价格'+@sql+'from #tb group by 商品,价格 order by 商品' )
drop table #tb
/*
商品价格商店1销量商店2销量商店3销量
商品115商店13商店25商店36
商品210商店14商店26商店35
商品313商店17商店25商店36
*/
drop table t
create table t(商品 varchar(100),
价格 int,
商店 varchar(100),
销量 int)
insert into t
values('商品1',15,'商店1',3)
insert into t
values('商品1',15,'商店2',5)
insert into t
values('商品1',15,'商店3',6)
insert into t
values('商品2',15,'商店1',4)
insert into t
values('商品2',15,'商店2',6)
insert into t
values('商品2',15,'商店3',5)
insert into t
values('商品3',15,'商店1',7)
insert into t
values('商品3',15,'商店2',5)
insert into t
values('商品3',15,'商店3',6)
declare @sql varchar(4000);
set @sql = '';
select @sql= @sql + ',max(case when 商店= ''' + 商店 +''' then 商店 else null end) as ' + 商店 +
+ ',max(case when 商店= ''' + 商店 +''' then 销量 else null end) as ' + 商店 + '_销量'
from t
group by 商店
set @sql = 'select 商品,价格 ' + @sql + +
' from t' +
' group by 商品,价格'
select @sql
/*
(无列名)
select 商品,价格 ,max(case when 商店= '商店1' then 商店 else null end) as 商店1,
max(case when 商店= '商店1' then 销量 else null end) as 商店1_销量,
max(case when 商店= '商店2' then 商店 else null end) as 商店2,
max(case when 商店= '商店2' then 销量 else null end) as 商店2_销量,
max(case when 商店= '商店3' then 商店 else null end) as 商店3,
max(case when 商店= '商店3' then 销量 else null end) as 商店3_销量
from t
group by 商品,价格
*/
exec(@sql)
/*
商品 价格商店1商店1_销量商店2商店2_销量商店3商店3_销量
商品115商店13商店25商店36
商品215商店14商店26商店35
商品315商店17商店25商店36
*/
create table cc
(商品 varchar(10),价格 int,商店 varchar(10),销量 int)
insert into cc
select '商品1',15,'商店1',3 union all
select '商品1',15,'商店2',5 union all
select '商品1',15,'商店3',6 union all
select '商品2',10,'商店1',4 union all
select '商品2',10,'商店2',6 union all
select '商品2',10,'商店3',5 union all
select '商品3',13,'商店1',7 union all
select '商品3',13,'商店2',5 union all
select '商品3',13,'商店3',6
-- 方法1
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'max(case when 商店='''+商店+''' then '''+商店+''' else '''' end) ''商店'', '
+'max(case when 商店='''+商店+''' then 销量 else 0 end) ''销量'' '
from (select distinct 商店 from cc) t
select @tsql='select 商品,价格,'+@tsql
+' from cc '
+' group by 商品,价格 '
+' order by 商品 '
exec(@tsql)
/*
商品 价格 商店 销量 商店 销量 商店 销量
---------- ----------- ----- ----------- ----- ----------- ----- -----------
商品1 15 商店1 3 商店2 5 商店3 6
商品2 10 商店1 4 商店2 6 商店3 5
商品3 13 商店1 7 商店2 5 商店3 6
(3 row(s) affected)
*/
-- 方法2
declare @tsql varchar(6000),@tsql2 varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+rtrim(number)+']',
@tsql2=isnull(@tsql2+',','')+'['+rtrim(number)+'] '''
+case when number%2=0 then '销量' else '商店' end+''' '
from master.dbo.spt_values
where type='P' and number between 1 and
(select max(rn)*2 from
(select row_number() over(order by 商店) 'rn'
from (select distinct 商店 from cc) t) x)
select @tsql='select 商品,价格,'+@tsql2
+' from (select 商品,价格,商店 ''x'',
row_number() over(partition by 商品,价格 order by 商店)*2-1 ''rn''
from cc
union all
select 商品,价格,rtrim(销量) ''x'',
row_number() over(partition by 商品,价格 order by 商店)*2 ''rn''
from cc) t '
+' pivot(max(x) for rn in('+@tsql+')) p '
+' order by 商品 '
exec(@tsql)
/*
商品 价格 商店 销量 商店 销量 商店 销量
---------- ----------- ------------ ------------ ------------ ------------ ------------ ------------
商品1 15 商店1 3 商店2 5 商店3 6
商品2 10 商店1 4 商店2 6 商店3 5
商品3 13 商店1 7 商店2 5 商店3 6
(3 row(s) affected)
*/
