SQL动态转置

Question

原数据
Id V 商品名称 字段 改动前的值 改动后的值
10998 1 电脑 数量 1 2
10998 1 桌子 数量 1 2
10998 1 电脑 单价 1000 1200
10998 1 桌子 单价 200 240
10998 2 电脑 数量 2 3
10998 2 桌子 数量 2 3
10998 2 电脑 单价 1200 1100
10998 2 桌子 单价 240 220

怎么才能实现以下效果
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请大侠们帮忙。先谢了

Answer 1

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 字段 descselect @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)
*/

Answer 2

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 行受影响)
*/

Answer 3

;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.商品名称/*
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
*/

Answer 4

方法2,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,220declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')
            +'['+字段+'改前],['+字段+'改后] '
 from (select distinct 字段 from mh) t 
 order by 字段 descselect @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)
*/

Answer 5

是这个不，通过动态语句，试试：if OBJECT_ID('tb') is not null
  drop table tb
gocreate 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,220declare @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 [字段] descselect @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 值。
*/

Answer 6

这是生成的动态语句，能适应“字段”的增加，比如你的数据中是数量、价格，再增加一个字段，也可以适应。动态语句已经经过格式化：
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,
         [商品名称]