SQL 多行多列互相转换问题

SQL Server 2005有表如下（字段内容如下）
      TypeName   Type    Spec1   Spec2    Spec3    Spec4
         A         0       100     102     0       200
         A         1        2       1      0        4
         A         2        2.2     1      0        4.5
         B         0       105     108     112      0
         B         1        3       1      5        0
         B         2        3.2     1      5.2      0
.......多条记录

行列互相转换后实现为：

TypeName  Spec    Type1    Type2
  A        100      2       2.2
  A        102      1        1
  A         0       0        0
  A        200     4       4.5  
  B        105     3       3.2  
  B        108     1       1    
....

普通的列转行、行转列从网上学到了，要实现多列（列数还有，最好动态）互相转化，请指教...

9 个解决方案

#1

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-03-30 16:26:48
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([TypeName] NVARCHAR(10),[Type] INT,[Spec1] DECIMAL(18,1),[Spec2] DECIMAL(18,1),[Spec3] DECIMAL(18,1),[Spec4] DECIMAL(18,1))
INSERT [tb]
SELECT 'A',0,100,102,0,200 UNION ALL
SELECT 'A',1,2,1,0,4 UNION ALL
SELECT 'A',2,2.2,1,0,4.5 UNION ALL
SELECT 'B',0,105,108,112,0 UNION ALL
SELECT 'B',1,3,1,5,0 UNION ALL
SELECT 'B',2,3.2,1,5.2,0
GO
--SELECT * FROM [tb]

-->SQL查询如下:
SELECT TYPENAME,SPEC=TYPE0,TYPE1,TYPE2
FROM (
	SELECT TYPENAME,'TYPE'+LTRIM(TYPE) TYPE,NUM,AMT
	FROM TB 
		UNPIVOT(AMT FOR NUM IN([SPEC1],[SPEC2],[SPEC3],[SPEC4])) UNP
	) AS T
	PIVOT(MAX(AMT) FOR TYPE IN(TYPE1,TYPE2,TYPE0)) P
/*
TYPENAME	SPEC	TYPE1	TYPE2
A	100.0	2.0	2.2
B	105.0	3.0	3.2
A	102.0	1.0	1.0
B	108.0	1.0	1.0
A	0.0	0.0	0.0
B	112.0	5.0	5.2
A	200.0	4.0	4.5
B	0.0	0.0	0.0
*/

#2

没看懂,友情UP....

#3

create table #ttt(TypeName  varchar(10),Type int, Spec1 float,Spec2 float,Spec3 float,Spec4 float)
insert #ttt select 'A', 0, 100, 102 ,0 ,200
insert #ttt select 'A', 1, 2, 1, 0 ,4
insert #ttt select 'A', 2, 2.2, 1, 0, 4.5
insert #ttt select 'B', 0, 105, 108 ,112, 0
insert #ttt select 'B', 1, 3, 1, 5 ,0
insert #ttt select 'B', 2, 3.2, 1 ,5.2 ,0

select TypeName,[0] as Spec,[1] as Type1,[2] as Type2 
from (
select TypeName,Type,tt,[values] from #ttt
unpivot([values] for tt in([Spec1], [Spec2], [Spec3] ,[Spec4])) unpvt
) a
pivot (max([values]) for type in([0],[1],[2])) pvt
order by TypeName

TypeName                     Spec                  Type1                  Type2
---------- ---------------------- ---------------------- ----------------------
A                             100                      2                    2.2
A                             102                      1                      1
A                               0                      0                      0
A                             200                      4                    4.5
B                             105                      3                    3.2
B                             108                      1                      1
B                             112                      5                    5.2
B                               0                      0                      0

(8 行受影响)

#4

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([TypeName] NVARCHAR(10),[Type] INT,[Spec1] DECIMAL(18,1),[Spec2] DECIMAL(18,1),[Spec3] DECIMAL(18,1),[Spec4] DECIMAL(18,1))
INSERT [tb]
SELECT 'A',0,100,102,0,200 UNION ALL
SELECT 'A',1,2,1,0,4 UNION ALL
SELECT 'A',2,2.2,1,0,4.5 UNION ALL
SELECT 'B',0,105,108,112,0 UNION ALL
SELECT 'B',1,3,1,5,0 UNION ALL
SELECT 'B',2,3.2,1,5.2,0
GO
--SELECT * FROM [tb]

-->SQL2005动态查询如下:
DECLARE @A VARCHAR(MAX),@B VARCHAR(MAX),@C VARCHAR(MAX)
SELECT @A=ISNULL(@A+',','')+QUOTENAME(NAME) 
FROM SYSCOLUMNS 
WHERE OBJECT_ID('TB')=ID AND NAME NOT IN('TYPENAME','TYPE')
SELECT @B=ISNULL(@B+',','')+QUOTENAME('TYPE'+LTRIM(TYPE)) FROM TB GROUP BY TYPE 
SELECT @C=STUFF(@B,1,CHARINDEX(',',@B),'')
EXEC('
	SELECT TYPENAME,SPEC=TYPE0,'+@C+'
	FROM (
		SELECT TYPENAME,''TYPE''+LTRIM(TYPE) TYPE,NUM,AMT
		FROM TB 
			UNPIVOT(AMT FOR NUM IN('+@A+')) UNP
		) AS T
		PIVOT(MAX(AMT) FOR TYPE IN('+@B+')) P'
)
/*
TYPENAME	SPEC	TYPE1	TYPE2
A	100.0	2.0	2.2
B	105.0	3.0	3.2
A	102.0	1.0	1.0
B	108.0	1.0	1.0
A	0.0	0.0	0.0
B	112.0	5.0	5.2
A	200.0	4.0	4.5
B	0.0	0.0	0.0
*/

#5

谢谢楼上几位！ 不好意思 好像落下一列 应该实现为：
行列互相转换后实现为：

TypeName SpecLoc Type0 Type1 Type2
A Spec1 100 2 2.2
A Spec2 102 1 1
A Spec3 0 0 0
A Spec4 200 4 4.5 
B Spec1 105 3 3.2 
B Spec2 108 1 1 
....

楼上基本差不多了 我试试

#6

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([TypeName] NVARCHAR(10),[Type] INT,[Spec1] DECIMAL(18,1),[Spec2] DECIMAL(18,1),[Spec3] DECIMAL(18,1),[Spec4] DECIMAL(18,1))
INSERT [tb]
SELECT 'A',0,100,102,0,200 UNION ALL
SELECT 'A',1,2,1,0,4 UNION ALL
SELECT 'A',2,2.2,1,0,4.5 UNION ALL
SELECT 'B',0,105,108,112,0 UNION ALL
SELECT 'B',1,3,1,5,0 UNION ALL
SELECT 'B',2,3.2,1,5.2,0
GO
--SELECT * FROM [tb]

-->SQL2005动态查询如下:
DECLARE @A VARCHAR(MAX),@B VARCHAR(MAX)
SELECT @A=ISNULL(@A+',','')+QUOTENAME(NAME) 
FROM SYSCOLUMNS 
WHERE OBJECT_ID('TB')=ID AND NAME NOT IN('TYPENAME','TYPE')
SELECT @B=ISNULL(@B+',','')+QUOTENAME('TYPE'+LTRIM(TYPE)) FROM TB GROUP BY TYPE 
EXEC('
    SELECT TYPENAME,SPEC='+@B+'
    FROM (
        SELECT TYPENAME,''TYPE''+LTRIM(TYPE) TYPE,NUM,AMT
        FROM TB 
            UNPIVOT(AMT FOR NUM IN('+@A+')) UNP
        ) AS T
        PIVOT(MAX(AMT) FOR TYPE IN('+@B+')) P
    ORDER BY 1'
)
/*
TYPENAME   SPEC                                    TYPE1                                   TYPE2
---------- --------------------------------------- --------------------------------------- ---------------------------------------
A          100.0                                   2.0                                     2.2
A          102.0                                   1.0                                     1.0
A          0.0                                     0.0                                     0.0
A          200.0                                   4.0                                     4.5
B          105.0                                   3.0                                     3.2
B          108.0                                   1.0                                     1.0
B          112.0                                   5.0                                     5.2
B          0.0                                     0.0                                     0.0

(8 行受影响)
*/

没落下一列吧,你看到漏哪列了?

#7

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)


select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]
declare @sql varchar(1000)
set @sql='select UserName'
select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a

set @sql = @sql + ' from tb group by UserName'
print @sql
exec(@sql) 


--讲解:

--这个是第一次执行
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], 
--这个是第二次
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , 
--这个是第三次
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文] 
--这个的数量来自于
(select distinct 课程 from tb)--这里只有3们课程



create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

create function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(value as varchar) from tb where id = @id
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select id , value = dbo.f_str(id) from tb group by id

drop function dbo.f_str
drop table tb


我只说一个地方 
select @str = @str + ',' + cast(value as varchar) from tb where id = @id 
你把这个看懂就明白了 
例如当@id=1 
select @str = @str + ',' + cast(value as varchar) from tb where id = 1 
把满足id=1的str值通过','累加 
当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后 
在查询满足2的直到所有的ID完为止 
这样明白了吧 




本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999394.aspx

#8

To : htl258
   谢谢！ 不是你落下了 是我发帖时候落下一列，表列名Spec1 Spec2 Spec3 Spec4 需要转换
为一列上SpecLoc显示

TypeName SpecLoc   Type0   Type1    Type2
A       Spec1        100    2        2.2
A       Spec2        102    1         1
A       Spec3         0     0         0
A       Spec4        200    4        4.5 
B       Spec1        105    3        3.2 
B       Spec2        108    1         1 

#9

引用 8 楼 wuyongbiao 的回复:

To : htl258
谢谢！ 不是你落下了 是我发帖时候落下一列，表列名Spec1 Spec2 Spec3 Spec4 需要转换
为一列上SpecLoc显示

TypeName SpecLoc Type0 Type1 Type2
A Spec1 100 2 2.2
A Spec2 102 1 1
A Spec3 0 0 0
A Spec4 200 4 4.5
B Spec1 1……

create table #ttt(TypeName  varchar(10),Type int, Spec1 float,Spec2 float,Spec3 float,Spec4 float)
insert #ttt select 'A', 0, 100, 102 ,0 ,200
insert #ttt select 'A', 1, 2, 1, 0 ,4
insert #ttt select 'A', 2, 2.2, 1, 0, 4.5
insert #ttt select 'B', 0, 105, 108 ,112, 0
insert #ttt select 'B', 1, 3, 1, 5 ,0
insert #ttt select 'B', 2, 3.2, 1 ,5.2 ,0

select TypeName,Spec,[0] as Type0,[1] as Type1,[2] as Type2 
from (
select TypeName,Type,Spec,[values] from #ttt
unpivot([values] for Spec in([Spec1], [Spec2], [Spec3] ,[Spec4])) unpvt
) a
pivot (max([values]) for type in([0],[1],[2])) pvt
order by TypeName

TypeName   Spec                                  Type0                  Type1                  Type2
---------- -------------------- ---------------------- ---------------------- ----------------------
A          Spec1                                   100                      2                    2.2
A          Spec2                                   102                      1                      1
A          Spec3                                     0                      0                      0
A          Spec4                                   200                      4                    4.5
B          Spec1                                   105                      3                    3.2
B          Spec2                                   108                      1                      1
B          Spec3                                   112                      5                    5.2
B          Spec4                                     0                      0                      0

(8 行受影响)