SQL列转行的问题！

Question

怎么样将下面的列XM转换成行，如：
XM
01
02
03转成：
XM1  XM2  XM3
01   02   03请问这样的SQL语句怎么写啊？谢谢了。
我的最终目的是要得到一个字符串01,02,03.

Answer 1

问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表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. 旧的解决方法-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id-- 2. 新的解决方法(适用于2005及以后版本)
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/CSDN 社区帖子地址 附: 合并与分拆的CLR， sql2005的示例中有：
在安装sql 2005的示例后，默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中

Answer 2

DECLARE @STR VARCHAR(1000)
SET @STR=''SELECT @STR=@STR+','+data
FROM tbSELECT STUFF(@STR,1,1,'')

Answer 3

http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 4

DECLARE @TB TABLE(XM VARCHAR(2))
INSERT @TB
SELECT '01' UNION ALL 
SELECT '02' UNION ALL 
SELECT '03'DECLARE @STR VARCHAR(100)
SET @STR=''
SELECT @STR=@STR+','+XM FROM @TBSELECT STUFF(@STR,1,1,'') AS XM
/*
XM                                                                                                  
--------------------------------------------------------------------------------------------------- 
01,02,03
*/

Answer 5

2000用臨時表處理；05用row_number

Answer 6

use tempdb
go
create table #(XM nvarchar(2))
insert # select '01'
insert # select '02'
insert # select '03'go
declare @s nvarchar(1000),@i int
select @s='',@i=count(*) from #
while @i>0
select @s=',[XM'+rtrim(@i)+']=max(case when row='+rtrim(@i)+' then xm else '''' end)'+@s,@i=@i-1
set @s=stuff(@s,1,1,'')
exec('select '+@s+' from (select row=row_number()over(order by (select 1)),xm from #)t')XM1  XM2  XM3
---- ---- ----
01   02   03(1 個資料列受到影響)

Answer 7

/*
将表数据旋转90度(2007-11-19于海南三亚)将下表数据：
A                    b           c           d           e           
-------------------- ----------- ----------- ----------- ----------- 
x                    1           2           3           4
y                    5           6           7           8
z                    9           10          11          12转化成如下结果：
a                    x          y          z          
-------------------- ---------- ---------- ---------- 
b                    1          5          9
c                    2          6          10
d                    3          7          11
e                    4          8          12*/--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go--生成中间数据表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(20)declare t_cursor cursor for 
select name from syscolumns 
where id=object_id('test1') and colid > 1 order by colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status = 0
begin
    exec('select ' + @name + ' as t into test3 from test1')
    set @s='insert into test2 select ''' + @name + ''''
    select @s = @s + ',''' + rtrim(t) + '''' from test3
    exec(@s)
    exec('drop table test3')
    fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor--查看行列互换处理结果
select * from test1
select * from test2--删除表
drop table test1
drop table test2
----------------------------------------------------------------------------
/*固定的写法:*/
select t1.* , t2.y , t3.z from
(select a = 'b' , x = b from test1 where a = 'x') t1, 
(select a = 'b' , y = b from test1 where a = 'y') t2,
(select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'c' , x = c from test1 where a = 'x') t1, 
(select a = 'c' , y = c from test1 where a = 'y') t2,
(select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'd' , x = d from test1 where a = 'x') t1, 
(select a = 'd' , y = d from test1 where a = 'y') t2,
(select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'e' , x = e from test1 where a = 'x') t1, 
(select a = 'e' , y = e from test1 where a = 'y') t2,
(select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a----------------------------------------------------------------------------
/*
表tb,数据如下:
项目种类  业绩  提成
洗吹类　　200   10
外卖      100   5
合计      300   15
转换成:
项目种类  洗吹类  外卖  合计
业绩      200     100   300
提成      10      5     15
*/create table tb
(
  项目种类 varchar(10),
  业绩     int,
  提成     int
)insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖'  ,100,5)
insert into tb(项目种类,业绩,提成) values('合计'  ,300,15)
goselect 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
  select 项目种类 = '业绩',
         洗吹类   = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
         外卖     = case when 项目种类 = '外卖'   then 业绩 else 0 end,
         合计     = case when 项目种类 = '合计'   then 业绩 else 0 end
  from tb
union all
  select 项目种类 = '提成' ,
         洗吹类   = case when 项目种类 = '洗吹类' then 提成 else 0 end,
         外卖     = case when 项目种类 = '外卖'   then 提成 else 0 end,
         合计     = case when 项目种类 = '合计'   then 提成 else 0 end
  from tb
) m
group by 项目种类
order by 项目种类 descdrop table tb/*
项目种类 洗吹类      外卖        合计          
-------- ----------- ----------- ----------- 
业绩     200         100         300
提成     10          5           15（所影响的行数为 2 行）
*/--------------------------------------------------------------------------
/*
数据库中tb表格如下
 
月份    工资   福利  奖金
1月     100    200   300
2月     110    210   310
3月     120    220   320
4月     130    230   330我想得到的结果是项目   1月    2月  3月  4月
工资   100    110  120  130
福利   200    210  220  230
奖金   300    310  320  330就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？
*/if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
       @tbname sysname, --要处理的表名
       @fdname sysname, --做为转换的列名
       @new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
        @s3 varchar(8000) , @s4 varchar(8000),
        @s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
       else @new_fdname + '=' end + '''''' + name + '''''''',
       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 
       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
       @s5 = @s5 + '+'' union all ''+@' + @i,
       @i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
       @s2=substring(@s2,2,8000),
       @s4=substring(@s4,2,8000),
       @s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go--用上面的存储过程测试：create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test 
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
goexec p_zj 'Test', '月份' , '项目'drop table Test
drop proc p_zj/*
项目   1月         2月         3月         4月          
---- ----------- ----------- ----------- ----------- 
福利   200         210         220         230
工资   100         110         120         130
奖金   300         310         320         330（所影响的行数为 3 行）
*//*
静态写法(SQL2005)
*/
--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--测试语句
SELECT * FROM 
(
  SELECT 考核月份,月份,金额 FROM 
     (SELECT 月份, 工资, 福利, 奖金 FROM Test) p
  UNPIVOT
     (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额)  FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt--测试结果/*
考核月份  1月     2月      3月     4月
-------  -----  -----   ------  -------
福利200210220230
工资100110120130
奖金300310320330
*/--删除环境
Drop table Test

Answer 8

我的最终目的是要得到一个字符串01,02,03. 
/*如何将一列中所有的值一行显示
数据源
  a
  b
  c
  d
  e
结果
a,b,c,d,e
*/create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)
/*
result     
---------- 
a,b,c,d,e,
*/--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/drop table tb

Answer 9

create TABLE tb(col VARCHAR(2))
INSERT tb
SELECT '01' UNION ALL 
SELECT '02' UNION ALL 
SELECT '03'--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)
/*
result   
-------- 
01,02,03
*/--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
01,02,03
*/
drop table tb