求sql，把行变成列，最好一个sql搞定！

例如：

col1   col2
 a       3
 b       98
 f       6

结果要为：
 a     b     f
 3     98    6

注意：col1,col2中数据内容不固定！

12 个解决方案

#1

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case col1 when '+col1+' then col2 else 0 end) '+col1 from t
set @sql=right(@sql,len(@sql)-1)
exec('select '+@sql+' from t')

#2

-- zt

create table t1(test1 varchar(10),test2 varchar(10),test3 varchar(10))
insert t1 
select 'a','b','c1'
union all select 'a','b','c11'
union all select 'e','f','c2'
union all select 'e','f','c22'


CREATE Function Result
(
@v1 nvarchar(30),
@v2 nvarchar(30)
)
returns varchar(400)
as
begin
declare @str1 varchar(400)
set @str1=''
select @str1=@str1 + IsNull(test3,'') from [t1] where test1=@v1 and test2=@v2
return @str1
end

select DISTINCT test1,test2,dbo.Result(test1,test2) as test3 from t1

drop table t1
drop function Result

/*
test1      test2       test3
-------------------------------
a b c1c11
e f c2c22
*/

#3

--- 回复人： jinjazz(我是jin) ( )   2004-05-08 10:37:38Z 

主表数据:编号   公司姓名    
          1     sgy
          2     aaa
          3     bbb
从表数据 公司编号  联系人
          1         甲
          1         乙
          1         丙
查询出这样的结果:公司名称    联系人
                   sgy       甲,乙,丙 
-----------------------------------------
谢谢，星期一结贴。

9:13 2004-5-8
问题解决,用自定义函数!
CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10))
INSERT INTO person_info
SELECT '資訊部','軟件編碼員','小李'
UNION ALL SELECT '資訊部','軟件編碼員','小王'
UNION ALL SELECT '行政部','秘書','小芳'

select * from person_info

--合並函數
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB
RETURN(substring(@r,2,8000))
END
GO


--删除测试
DROP TABLE person_info
DROP FUNCTION FunMergeCharField



--調用
select * from person_info

SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name) 
FROM person_info 
GROUP BY dept_name,position_name
go
参考CSDN以前的贴子的!

#4

我用风云的方法怎么得不到正确的结果？

#5

老大们，我的问题是用一句sql搞定，不是用自定义函数！

#6

有没有什么简单的方法呢？

#7

只要能够搞定什么都好

#8

内容不固定，一句搞定是不可能的

#9

用了风云的方法,怎么报错 "在关键字 'then' 附近有语法错误。"

#10

报错是因为你写的不对

#11

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case col1 when '''+col1+''' then col2 else 0 end) '+col1 from t
set @sql=right(@sql,len(@sql)-1)
exec('select '+@sql+' from t')

#12

-----引 relgz
2、纵横转换

a、横到纵
有表 unknown
name a c b d
Tom 1 2 3 4
Sun 1 2 3 4
要求以纵向格式显示
这个最简单一直union 下来就是 
select name,'a' as item,a as value from unknown
union all
select name,'b' as item,b as value from unknown
union all
select name,'c' as item,c as value from unknown
union all
select name,'d' as item,d as value from unknown

结果有人说了，你这个格式不符合要求，怎么一会儿Tom，一会儿 Sum 的看得我都晕了，我要按照
tom a 1
tom c 2
tom b 3
tom d 4
Sun a 1
这样的格式排下来，你心里可能会开始骂：真多事，给你列出来不就得了，还这么多要求
尝试：
select * from 
(
select name,'a' as item,a as value from unknown
union all
select name,'b' as item,b as value from unknown
union all
select name,'c' as item,c as value from unknown
union all
select name,'d' as item,d as value from unknown
) view1 
order by name,item

或

select name,'a' as item,a as value from unknown
union 
select name,'b' as item,b as value from unknown
union 
select name,'c' as item,c as value from unknown
union 
select name,'d' as item,d as value from unknown

结果又有人挑骨头：在我的表里c是在b前面的，你怎么跑后面去了！
想到最后，聪明的你被逼无奈最后一定会想出了很下流的一招：系统表。
select view1.* from 
(
select name,'a' as item,a as value from unknown
union all
select name,'b' as item,b as value from unknown
union all
select name,'c' as item,c as value from unknown
union all
select name,'d' as item,d as value from unknown
) view1 ,
syscolumns s
where view1.item *= s.name where id=object_id('unknown')
order by view1.name,s.colid
--注意这个*= 不是笔误，是 left outer join 的意思
a、纵到横
这世道总有人想不开，你刚把数据转成了 unknown2
name item value
tom a 1
tom c 2
tom b 3
tom d 4
Sun a 1
Sun c 2
Sun b 3
Sun d 4
又有人要你把它转回来。
没办法，开始分析：
转回来的意思就是行只剩下 Tom和 Sun 两个单位了，列却要多了起来，等等，列要多起来？什么时候会多起来？自然是join 了！好:

select t0.name,t1.value as 'a',t2.value as 'c',t3.value as 'b', t4.value as 'd'
from 
(select distinct name from unknown2) t0,
(select name,item from unknown2 where item='a') t1,
(select name,item from unknown2 where item='c') t2,
(select name,item from unknown2 where item='b') t3,
(select name,item from unknown2 where item='d') t4
where 
t0.name*=t1.name and t0.name*=t2.name and t0.name*=t3.name and t0.name*=t4.name

但如果用另外一个方向想，怎么才能把行压缩成两行呢？只有group by，但怎么区分在不同列里显示的不同内容呢？最好的答案是case：
：
select name,
sum(case when item='a' then value else 0 end) as a,
sum(case when item='c' then value else 0 end) as c,
sum(case when item='b' then value else 0 end) as b,
sum(case when item='d' then value else 0 end) as d
from unknown2
group by name

至于其他再复杂的纵横转换，也就是上面几种思路的交叉而已了。
至于如果设计 列数待定的情况则只能用过程来解决了。


附：一个通用从纵到横的简单转换存储过程
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
/*
过程作用，根据纵向数据生成新横向结构
by realgz@csdn 2003-12-26
*/
as
declare @exec varchar(8000)

create table #tmp (col varchar(255))

set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''

select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then ['+@col_to_turn +'] else null end ) as ['+col+'],'
from #tmp 
set @exec=left(@exec,len(@exec)-1)
set @exec='select ['+@key_col+'],'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+']'

exec(@exec)