怎样把纵向数据转化为横向数据(MSSQL)

Question

说明：我使用的是MS SQL2000数据库
原始数据：
大类 货品编号 零件编号 用量
D1   A1       B11    10
D1   A1       B21    6
D1   A1       B31    3
D2   A2       B21    5
D2   A2       B31    3
D3   A3       B11    9
D3   A3       B31    3
......
请问怎样用MS SQl 2000语句转化为如下用量对照表(货品编号A1   A2   A3  .....是不固定的）：
零件编号 A1   A2   A3  .....
B11      10        9
B21      6     5
B31      3     3   3

Answer 1

行列转置.
参见:
http://blog.csdn.net/qianjin036a/article/details/6582237

Answer 2

列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78(8 行受影响)
*/

Answer 3

行转列问题总结 - 1、行转列 （后面不断整理论坛中出现的各类问题）---1、最简单的行转列
/*    问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
李四 74   84   94
张三 74   83   93
*/
--测试用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) 
--通过动态构建@sql，得到如下脚本
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
--得到SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--查询结果
/*
姓名         数学          物理          语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74（所影响的行数为 2 行）
*/

Answer 4

Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78要转化为
Course 李四      张三
------- ------- -----------
数学-----77-----87
物理-----85-----90
英语-----65-----82
语文-----65-----78

Answer 5

这个方法可行，但declare @sql varchar(8000)还是不够长，因为数据太多
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) 

Answer 6

如果A1 A2 A3..不是很多的话,用case when then...吧

Answer 7

就是因为A1 A2 A3...太多

Answer 8

--动态行转列
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 大类 varchar(10),
 货品编号 varchar(10),
 零件编号 varchar(10),
 用量 int
)
go
insert into tb
select 'D1','A1','B11',10 union all
select 'D1','A1','B21',6 union all
select 'D1','A1','B31',3 union all
select 'D2','A2','B21',5 union all
select 'D2','A2','B31',3 union all
select 'D3','A3','B11',9 union all
select 'D3','A3','B31',3
go
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+货品编号+'=sum(case when 货品编号='''+货品编号+''' then 用量 else 0 end)' from (select distinct 货品编号 from tb) t
print 'select 零件编号,'+@sql+' from tb group by 零件编号'
exec('select 零件编号,'+@sql+' from tb group by 零件编号')
/*
select 零件编号,A1=sum(case when 货品编号='A1' then 用量 else 0 end),A2=sum(case when 货品编号='A2' then 用量 else 0 end),A3=sum(case when 货品编号='A3' then 用量 else 0 end) from tb group by 零件编号
零件编号       A1          A2          A3
---------- ----------- ----------- -----------
B11        10          0           9
B21        6           5           0
B31        3           3           3(3 行受影响)
*/

Answer 9

--创建表，插入数据：
go
if object_id('[tbl]')is not null
drop table [tbl]
go
create table[tbl](
kind varchar(5),
productid varchar(5),
salesid varchar(5),
needs int
)
go 
insert [tbl]
select 'D1','A1','B11',10 union all
select 'D1','A1','B21',6 union all
select 'D1','A1','B31',3 union all
select 'D2','A2','B21',5 union all
select 'D2','A2','B31',3 union all
select 'D3','A3','B11',9 union all
select 'D3','A3','B31',3
select *from [tbl]--创建函数实现：
--drop function dbo.f_switch
CREATE FUNCTION dbo.f_switch(@id varchar(5))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r=@r+' '+cast(needs as varchar)
    FROM [tbl]
    WHERE salesid=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt salesid,needs=dbo.f_switch(salesid) 
FROM [tbl] group by salesid
结果
salesid  needs
B11  10 9
B21  6 5
B31  3 3 3