SQL动态转置
原数据
IdV商品名称字段改动前的值改动后的值
109981电脑数量12
109981桌子数量12
109981电脑单价10001200
109981桌子单价200240
109982电脑数量23
109982桌子数量23
109982电脑单价12001100
109982桌子单价240220
怎么才能实现以下效果
IdV商品名称数量改前数量改后单价改前单价改后
109981电脑1210001200
109981桌子12200240
109982电脑2312001100
109982桌子23240220
请大侠们帮忙。先谢了
[解决办法]
实在是看不懂你的表是个怎么样子的
[解决办法]
create table mh
(Id int,V int,商品名称 varchar(10),字段 varchar(10),改动前的值 int,改动后的值 int)
insert into mh
select 10998,1,'电脑','数量',1,2 union all
select 10998,1,'桌子','数量',1,2 union all
select 10998,1,'电脑','单价',1000,1200 union all
select 10998,1,'桌子','单价',200,240 union all
select 10998,2,'电脑','数量',2,3 union all
select 10998,2,'桌子','数量',2,3 union all
select 10998,2,'电脑','单价',1200,1100 union all
select 10998,2,'桌子','单价',240,220
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'(select top 1 b.改动前的值 from mh b
where b.Id=a.Id and b.V=a.V and b.商品名称=a.商品名称 and b.字段='''+字段+''') '''+字段+'改前'', '
+'(select top 1 b.改动后的值 from mh b
where b.Id=a.Id and b.V=a.V and b.商品名称=a.商品名称 and b.字段='''+字段+''') '''+字段+'改后'' '
from (select distinct 字段 from mh) t order by 字段 desc
select @tsql='select a.Id,a.V,a.商品名称,'+@tsql
+' from mh a group by a.Id,a.V,a.商品名称 '
exec(@tsql)
/*
Id V 商品名称 数量改前 数量改后 单价改前 单价改后
----------- ----------- ---------- ----------- ----------- ----------- -----------
10998 1 电脑 1 2 1000 1200
10998 1 桌子 1 2 200 240
10998 2 电脑 2 3 1200 1100
10998 2 桌子 2 3 240 220
(4 row(s) affected)
*/
use MyTest
go
if OBJECT_ID('tb') is not null
drop table tb
create table tb(Id varchar(5),V int,[商品名称] nvarchar(10),[字段] nvarchar(10),[改动前的值] int,[改动后的值] int)
insert into tb
select '10998',1,'电脑','数量',1,2 union all
select '10998',1,'桌子','数量',1,2 union all
select '10998',1,'电脑','单价',1000,1200 union all
select '10998',1,'桌子','单价',200,240 union all
select '10998',2,'电脑','数量',2,3 union all
select '10998',2,'桌子','数量',2,3 union all
select '10998',2,'电脑','单价',1200,1100 union all
select '10998',2,'桌子','单价',240,220
select Id,V,[商品名称]
,MAX(case [字段] when '数量' then [改动前的值] else 0 end) as '数量改前'
,MAX(case [字段] when '数量' then [改动后的值] else 0 end) as '数量改后'
,MAX(case [字段] when '单价' then [改动前的值] else 0 end) as '单价改前'
,MAX(case [字段] when '单价' then [改动后的值] else 0 end) as '单价改后'
from tb
group by Id,V,[商品名称]
drop table tb
--结果
/**
Id V 商品名称 数量改前 数量改后 单价改前 单价改后
----- ----------- ---------- ----------- ----------- ----------- -----------
10998 1 电脑 1 2 1000 1200
10998 1 桌子 1 2 200 240
10998 2 电脑 2 3 1200 1100
10998 2 桌子 2 3 240 220
(4 行受影响)
*/
;with cte(Id,V,商品名称 ,字段 ,改动前的值 ,改动后的值 ) as
(
select 10998,1,'电脑','数量',1,2
union all select 10998,1,'桌子','数量',1,2
union all select 10998,1,'电脑','单价',1000,1200
union all select 10998,1,'桌子','单价',200,240
union all select 10998,2,'电脑','数量',2,3
union all select 10998,2,'桌子','数量',2,3
union all select 10998,2,'电脑','单价',1200,1100
union all select 10998,2,'桌子','单价',240,220
),
cte2 as
(
select *,rn=ROW_NUMBER() over(partition by Id,V,商品名称 order by getdate())
from cte
)
select a.Id,a.V,a.商品名称,b.改动前的值 as 数量改前,b.改动后的值 as 数量改后
,a.改动前的值 as 单价改前,a.改动后的值 as 单价改后
from cte2 a
inner join cte2 b on a.rn+1=b.rn and a.Id=b.Id and a.V=b.V and a.商品名称=b.商品名称
/*
IdV商品名称数量改前数量改后单价改前单价改后
109981电脑1210001200
109981桌子12200240
109982电脑2312001100
109982桌子23240220
*/
create table mh
(Id int,V int,商品名称 varchar(10),字段 varchar(10),改动前的值 int,改动后的值 int)
insert into mh
select 10998,1,'电脑','数量',1,2 union all
select 10998,1,'桌子','数量',1,2 union all
select 10998,1,'电脑','单价',1000,1200 union all
select 10998,1,'桌子','单价',200,240 union all
select 10998,2,'电脑','数量',2,3 union all
select 10998,2,'桌子','数量',2,3 union all
select 10998,2,'电脑','单价',1200,1100 union all
select 10998,2,'桌子','单价',240,220
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'['+字段+'改前],['+字段+'改后] '
from (select distinct 字段 from mh) t
order by 字段 desc
select @tsql='select Id,V,商品名称,'+@tsql
+' from (select Id,V,商品名称,字段+''改前'' ''col'',改动前的值 ''value'' from mh '
+' union all '
+' select Id,V,商品名称,字段+''改后'' ''col'',改动后的值 ''value'' from mh) a '
+' pivot(max(value) for col in ('+@tsql+')) p '
exec(@tsql)
/*
Id V 商品名称 数量改前 数量改后 单价改前 单价改后
----------- ----------- ---------- ----------- ----------- ----------- -----------
10998 1 电脑 1 2 1000 1200
10998 1 桌子 1 2 200 240
10998 2 电脑 2 3 1200 1100
10998 2 桌子 2 3 240 220
(4 row(s) affected)
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
Id varchar(5),
V int,
[商品名称] nvarchar(10),
[字段] nvarchar(10),
[改动前的值] int,
[改动后的值] int
)
insert into tb
select '10998',1,'电脑','数量',1,2 union all
select '10998',1,'桌子','数量',1,2 union all
select '10998',1,'电脑','单价',1000,1200 union all
select '10998',1,'桌子','单价',200,240 union all
select '10998',2,'电脑','数量',2,3 union all
select '10998',2,'桌子','数量',2,3 union all
select '10998',2,'电脑','单价',1200,1100 union all
select '10998',2,'桌子','单价',240,220
declare @sql nvarchar(3000);
set @sql = '';
select @sql =
@sql + ',min(case when [字段]=''' + [字段] + ''' then [改动前的值] else null end) as ['+[字段]+ '改前]'+
',min(case when [字段]=''' + [字段] + ''' then [改动后的值] else null end) as ['+[字段]+ '改后]'
from tb
group by [字段]
order by [字段] desc
select @sql = 'select id,v,[商品名称]'+@sql +
' from tb group by id,v,[商品名称]'
--输出动态语句
select @sql
exec(@sql)
/*
id v 商品名称 数量改前 数量改后 单价改前 单价改后
----- ----------- ---------- ----------- ----------- ----------- -----------
10998 1 电脑 1 2 1000 1200
10998 1 桌子 1 2 200 240
10998 2 电脑 2 3 1200 1100
10998 2 桌子 2 3 240 220
警告: 聚合或其他 SET 操作消除了 Null 值。
*/
select id,
v,
[商品名称],
min(case when [字段]='数量' then [改动前的值] else null end) as [数量改前],
min(case when [字段]='数量' then [改动后的值] else null end) as [数量改后],
min(case when [字段]='单价' then [改动前的值] else null end) as [单价改前],
min(case when [字段]='单价' then [改动后的值] else null end) as [单价改后]
from tb
group by id,
v,
[商品名称]
