100分求 SQL2005不用循环行转列，列转行问题

Question

--问题1，如数据1，不用循环输出结果1
--数据1
DECLARE @t TABLE (xm CHAR(4),ah CHAR(10))
INSERT @t  SELECT '宋明','打游戏'
UNION  ALL SELECT '宋明','睡觉'
UNION  ALL SELECT '宋明','吃饭'
UNION  ALL SELECT '王三','旅游'
UNION  ALL SELECT '王三','听音乐'
UNION  ALL SELECT '张三','打游戏'
--结果1
xm   ah
---- ----------------------------------------
宋明   打游戏,睡觉,吃饭                        
王三   旅游,听音乐                             
张三   打游戏   --问题2，如数据2，不用循环输出结果2
--数据2
DECLARE @tt TABLE (xm CHAR(4),ah CHAR(40))
INSERT @tt  SELECT '宋明','打游戏,睡觉,吃饭'
UNION  ALL SELECT '王三','旅游,听音乐'
UNION  ALL SELECT '张三','打游戏'
--结果2
xm   ah
---- ----------
宋明   打游戏    
宋明   睡觉      
宋明   吃饭      
王三   旅游      
王三   听音乐    
张三   打游戏 

Answer 1

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]GO
动态:declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337(2 行受影响)
*/go--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 行受影响)
*/
 
 
 
对我有用[0] 丢个板砖[0] 引用 举报 管理 TOP 精华推荐：SQL Server 2008 亮 

Answer 2

--1
select xm,ah=stuff((select ','+ah from @t where xm=t.xm for xml path('')),1,1,'')
from @t t
group by xm

Answer 3

--1DECLARE @t TABLE (xm CHAR(4),ah varCHAR(10))
INSERT @t  SELECT '宋明','打游戏'
UNION  ALL SELECT '宋明','睡觉'
UNION  ALL SELECT '宋明','吃饭'
UNION  ALL SELECT '王三','旅游'
UNION  ALL SELECT '王三','听音乐'
UNION  ALL SELECT '张三','打游戏'
select xm,
ah=stuff((select ','+ah from @t where xm=t.xm for xml path('')),1,1,'')
from @t t
group by xmxm   ah
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
宋明   打游戏,睡觉,吃饭
王三   旅游,听音乐
张三   打游戏(3 行受影响)

Answer 4

--2
DECLARE @tt TABLE (xm CHAR(4),ah CHAR(40))
INSERT @tt  SELECT '宋明','打游戏,睡觉,吃饭'
UNION  ALL SELECT '王三','旅游,听音乐'
UNION  ALL SELECT '张三','打游戏'select 
  a.xm,ah=substring(a.ah,b.id,charindex(',',a.ah+',',b.id)-b.id) 
from 
  @tt a,(select top 100 id=row_number() over(order by getdate()) from sys.columns,sys.objects)b
where
  charindex(',',','+a.ah,b.id)=b.id/**
xm   ah
---- ----------------------------------------
宋明   打游戏
王三   旅游
张三   打游戏                                  
王三   听音乐                             
宋明   睡觉
宋明   吃饭                        (6 行受影响)
**/

Answer 5

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/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,指课程只有语文、数学、物理这三门课程。(以下同)
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 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 SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--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')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--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 + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + '['+ltrim(课程名称)+']' from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb   
ALTER PROCEDURE [dbo].[pro_score]AS
declare @xq varchar(20)
declare @zy varchar(20)
declare @sql varchar(1000)select @sql = isnull(@sql + ',' , '') + '['+ltrim(课程名称)+']' from score_view group by 课程名称exec ('select m.*, n.平均分,n.总分 from (select * from (select 学号,姓名,学期,专业,课程名称,成绩 from score_view   ) a
pivot (max(成绩) for 课程名称 in ('+@sql+') )  b ) m ,
(select 学号, 学期,专业,cast(avg(成绩*1.0) as decimal(18,2))平均分,sum(成绩)总分 from score_view  group by 学号,学期,专业 having 学期=''20081'' and 专业=''软件技术'') n 
where m.学号 = n.学号 ')--20081   -- @xq
------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb   /*表结构，数据如下：  
id    value  
----- ------  
1    aa  
1    bb  
2    aaa  
2    bbb  
2    ccc  需要得到结果：  
id    values  
------ -----------  
1      aa,bb  
2      aaa,bbb,ccc  
即：group by id, 求 value 的和（字符串相加） */ 1. 旧的解决方法(在sql server 2000中只能用函数解决。)  
--====================================================== 
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  
--1. 创建处理函数 
CREATE FUNCTION dbo.f_strUnite(@id int)  
RETURNS varchar(8000)  
AS  
BEGIN  
    DECLARE @str varchar(8000)  
    SET @str = ''  
    SELECT @str = @str + ',' + value FROM tb WHERE id=@id  
    RETURN STUFF(@str, 1, 1, '')  
END  
GO  
-- 调用函数 
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id  
drop table tb  
drop function dbo.f_strUnite  
go 
/*  
id          value       
----------- -----------  
1          aa,bb  
2          aaa,bbb,ccc  
（所影响的行数为 2 行）  
*/  
--====================================================== 
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)  
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  
-- 查询处理 
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(  
        SELECT [values]= STUFF(REPLACE(REPLACE(  
            (  
                SELECT value FROM tb N  
                WHERE id = A.id  
                FOR XML AUTO  
            ), ' <N value="', ','), '"/>', ''), 1, 1, '')  
)N  
drop table tb  /*  
id          values  
----------- -----------  
1          aa,bb  
2          aaa,bbb,ccc  (2 行受影响)  
*/  --SQL2005中的方法2 
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  
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id  
for xml path('')), 1, 1, '')  
from tb  
group by id  
/*  
id          values  
----------- --------------------  
1          aa,bb  
2          aaa,bbb,ccc   
*/  drop table tb      /*有表tb, 如下:  
id          value  
----------- -----------  
1          aa,bb  
2          aaa,bbb,ccc  
欲按id,分拆value列, 分拆后结果如下:  
id          value  
----------- --------  
1          aa  
1          bb  
2          aaa  
2          bbb  
2          ccc */ 1. 旧的解决方法(sql server 2000)  
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number)  
FROM tb a, master..spt_values  b  
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ','  2. 新的解决方法(sql server 2005)  
create table tb(id int,value varchar(30))  
insert into tb values(1,'aa,bb')  
insert into tb values(2,'aaa,bbb,ccc')  
go  
SELECT a.id, b.value  
FROM(  
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb  
)a  
OUTER aPPLY(  
    SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v)  
)b  DROP TabLE tb  /*  
id          value  
----------- ------------------------------  
1          aa  
1          bb  
2          aaa  
2          bbb  
2          ccc  (5 行受影响)  
*/ 

Answer 6

DECLARE @tt TABLE (xm CHAR(4),ah varCHAR(40))
INSERT @tt  SELECT '宋明','打游戏,睡觉,吃饭'
UNION  ALL SELECT '王三','旅游,听音乐'
UNION  ALL SELECT '张三','打游戏'select xm,
substring( ah+',',number,charindex(',',ah+',',number)-number) as ah
from @tt,master..spt_values s
where s.number>0 and type='p'
and substring(','+ah,number,1)=','xm   ah
---- -----------------------------------------
宋明   打游戏
宋明   睡觉
宋明   吃饭
王三   旅游
王三   听音乐
张三   打游戏(6 行受影响)

Answer 7

谢谢各位，搞定。
又学到东西了。--问题1，如数据1，不用循环输出结果1
--数据1
DECLARE @t TABLE (xm CHAR(4),ah CHAR(10))
INSERT @t  SELECT '宋明','打游戏'
UNION  ALL SELECT '宋明','睡觉'
UNION  ALL SELECT '宋明','吃饭'
UNION  ALL SELECT '王三','旅游'
UNION  ALL SELECT '王三','听音乐'
UNION  ALL SELECT '张三','打游戏'
--结果1
--xm   ah
------ ----------------------------------------
--宋明   打游戏,睡觉,吃饭                         
--王三   旅游,听音乐                             
--张三   打游戏  
--SQL语句 
SELECT a.xm, ah =STUFF((
                        SELECT ','+ LTRIM(RTRIM(ah)) 
                        FROM @t b 
                        WHERE  a.xm = b.xm 
                        FOR XML PATH('')
                        ), 1, 1, '')  
FROM @t a 
GROUP BY a.xm --问题2，如数据2，不用循环输出结果2
--数据2
DECLARE @tt TABLE (xm CHAR(4),ah CHAR(40))
INSERT @tt  SELECT '宋明','打游戏,睡觉,吃饭'
UNION  ALL SELECT '王三','旅游,听音乐'
UNION  ALL SELECT '张三','打游戏'
--结果2
--xm   ah
------ ----------
--宋明   打游戏    
--宋明   睡觉      
--宋明   吃饭      
--王三   旅游      
--王三   听音乐    
--张三   打游戏 SELECT xm,
       SUBSTRING( ah+',',number,CHARINDEX(',',ah+',',number)-number) AS ah
FROM @tt,master..spt_values s
WHERE s.number > 0 and type = 'p'
      AND SUBSTRING (','+ah,number,1) = ','