[分享]SQL列转行

奥尔覅几22

在做一些视图，报表或者查询的时候，列转行是必须要做的，甚至有多列要转。
SQLSERVER中实现 基本思路是这样的
1  查出所需要数据放在视图1中，且在同一行中将第一次要转的字符串拼成1个长字符串；用+，相当于ORC的||
2  创建一个合并函数1 ，作用是动态SQL将在视图1中同一列中满足条件的字符串再拼成一个长字符串
3  调用这个合并函数1，将第一次列转行的数据存成视图2，同时也可将第二次列转行的同一行中要转的字符串拼成1个长字符串
4  创建一个合并函数2 ，作用是动态SQL将在视图1中同一列中满足条件的字符串再拼成一个长字符串
5  调用这个合并函数1，将第一次列转行的数据存成视图3...
以此类推...
注意：如果数据中有空值得话，用函数转换的时候会报substring 传递值错误。所以对于有空置的列，需要用isnull 判断赋值


可能上面的描述有点拗口，其实对于  SQL多列转行，先可以对现有数据分成几个大列，每次写一个合并函数，对其中的一个大列进行列转行操作。
操作后的数据，再针对其写第二个合并函数，继续对另外的大列进行列转行。直到满足要求为止。
我所找到的办法 基本思路就是这样了，感觉貌似是个笨办法。
很久没碰ORC了，不知道ORC有没有更好的办法。



上段代码，举个小例

--sql server 2000中的写法.
create table tb(name varchar(10), work varchar(10), startime int, endtime int , remark varchar(20))
_insert into tb values('张三', '程序员' , 1 , 3,'小伙子不错')
_insert into tb values('张三', '程序员' , 2 , null,'小伙子不错')
_insert into tb values('张三', '程序员' , 3 , null,'小伙子不错')
_insert into tb values('李四', '项目经理', 5 , null,'有前途')
_insert into tb values('李四', '项目经理', 7 , 10,'有前途')
_insert into tb values('王五', '技术总监', 10, null,'真棒')
go
--创建一个合并的函数
create function f_hb(@name varchar(10),@work varchar(10), @remark varchar(20))
returns varchar(8000)
as
begin
  _declare @str varchar(8000)
  set @str = ''
  _select @str = @str + ',' + cast(time as varchar) from
  (
    _select name,work,remark,time = case when endtime is not null then cast(startime as varchar) + '-' + cast(endtime as varchar) else cast(startime as varchar) end from tb
  ) t
  where name = @name and work = @work and remark = @remark
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go
--调用自定义函数得到结果：
_select distinct name ,work ,remark ,dbo.f_hb(name ,work ,remark) as time from tb
/*
name       work       remark               time
---------- ---------- -------------------- ------------------
李四         项目经理       有前途                  5,7-10
王五         技术总监       真棒                   10
张三         程序员        小伙子不错                1-3,2,3