sql行列转换的问题
watch_date dangwei zhihui jiguan xingzhen
------------------------------------------------------------
2009-2-3张朝娜 叶长勇 关炜 万慧阳 李中明 余茂菲 李金键王照华 陈开义 志勇
有值班表一张 csinfo_watch，其中放有各个部门每天值班的情况
现在需要把这些记录都转换成一列的形式来呈现
watch_date|watch
-----------------------
2009-2-3 张朝娜
2009-2-3 叶长勇
2009-2-3 关炜
2009-2-3 万慧阳
2009-2-3 李中明
2009-2-3 余茂菲
2009-2-3 李金键
2009-2-3 王照华
2009-2-3 陈开义
2009-2-3 志勇
-----------------------
大家帮忙想想办法啊
[最优解释]
SELECT watch_date,dangwei FROM csinfo_watch
UNION ALL
SELECT watch_date,zhihui FROM csinfo_watch
UNION ALL
SELECT
watch_date,
SUBSTRING(A.jiguan,B.number,CHARINDEX(' ',A.jiguan+' ',B.number)-B.number) AS watch
FROM csinfo_watch AS A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.jiguan)
AND SUBSTRING(' '+A.jiguan,B.number,1)=' '
UNION ALL
SELECT
watch_date,
SUBSTRING(A.xingzhen,B.number,CHARINDEX(' ',A.xingzhen+' ',B.number)-B.number) AS watch
FROM csinfo_watch AS A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.xingzhen)
AND SUBSTRING(' '+A.xingzhen,B.number,1)=' '
ORDER BY watch_date
[其他解释]
分割插入.
[其他解释]
部门名称就是“ dangwei，zhihui， jiguan，xingzhen ”？
[其他解释]
你给的数据里应该还要提供部门那一列的数据
[其他解释]
猜一个
---测试数据---
if object_id('[csinfo_watch]') is not null drop table [csinfo_watch]
go
create table [csinfo_watch]([watch_date] datetime,[watch] varchar(6),[dept] varchar(8))
insert [csinfo_watch]
select '2009-2-3','张朝娜','dangwei' union all
select '2009-2-3','叶长勇','zhihui' union all
select '2009-2-3','关炜','jiguan' union all
select '2009-2-3','万慧阳','jiguan' union all
select '2009-2-3','李中明','jiguan' union all
select '2009-2-3','余茂菲','jiguan' union all
select '2009-2-3','李金键','jiguan' union all
select '2009-2-3','王照华','xingzhen' union all
select '2009-2-3','陈开义','xingzhen' union all
select '2009-2-3','志勇','xingzhen'
---创建字符连接函数---
create function F_Str(@dept VARCHAR(10))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+' ','')+watch from csinfo_watch where dept=@dept
return @S
end
---查询---
select
watch_date,
max(case dept when 'dangwei' then watch else '' end) as [dangwei],
max(case dept when 'zhihui' then watch else '' end) as [zhihui],
max(case dept when 'jiguan' then watch else '' end) as [jiguan],
max(case dept when 'xingzhen' then watch else '' end) as [xingzhen]
from
(
select
distinct
watch_date,
dept,
dbo.f_str(dept) as watch
from [csinfo_watch]
) t
group by watch_date
---结果---
watch_date dangwei zhihui jiguan xingzhen
------------------------------ ----------- --------------------- -------------------------------------- -------------------------
2009-02-03 00:00:00.000 张朝娜 叶长勇 关炜 万慧阳 李中明 余茂菲 李金键 王照华 陈开义 志勇
（所影响的行数为 1 行）
[其他解释]
小梁的sql思路是正确的
但是有个关键的问题，可能是刚我需求表达的不够清楚
每个部门有几个人值班是不能固定的，比如周末人要少些，重要节假日人又会多些，这个怎么去处理呢？
[其他解释]
来迟了
[其他解释]
create table tb(watch_date datetime,dangwei nvarchar(20),zhihui nvarchar(30),jiguan nvarchar(30),xingzhen nvarchar(30))
insert into tb select '2009-2-3','张朝娜','叶长勇','关炜 万慧阳 李中明 余茂菲 李金键','王照华 陈开义 志勇'
insert into tb select '2009-2-4','aaa','bbb ccc','ddd eee','fff kkk'
go
select * into # from(
select watch_date,dangwei as watch from tb
union all
select watch_date,zhihui as watch from tb
union all
select watch_date,jiguan as watch from tb
union all
select watch_date,xingzhen as watch from tb
)T
;
with cte as(
select watch_date,convert(varchar,watch)as watch,convert(varchar(30),'') as flg from # where charindex(' ',watch)=0
union all
select watch_date,convert(varchar,left(watch,charindex(' ',watch))) as watch,right(watch,len(watch)-charindex(' ',watch))+' ' as flg from # where charindex(' ',watch)>1
union all
select watch_date,convert(varchar,left(flg,charindex(' ',flg))) as watch,right(flg,len(flg)-charindex(' ',flg)+1) as flg from cte where charindex(' ',flg)>1
)select watch_date,watch from cte order by watch_date
go
drop table tb,#
/*
watch_date watch
----------------------- ------------------------------
2009-02-03 00:00:00.000 张朝娜
2009-02-03 00:00:00.000 叶长勇
2009-02-03 00:00:00.000 关炜
2009-02-03 00:00:00.000 王照华
2009-02-03 00:00:00.000 万慧阳
2009-02-03 00:00:00.000 李中明
2009-02-03 00:00:00.000 余茂菲
2009-02-03 00:00:00.000 李金键
2009-02-03 00:00:00.000 陈开义
2009-02-03 00:00:00.000 志勇
2009-02-04 00:00:00.000 eee
2009-02-04 00:00:00.000 ccc
2009-02-04 00:00:00.000 fff
2009-02-04 00:00:00.000 kkk
2009-02-04 00:00:00.000 ddd
2009-02-04 00:00:00.000 bbb
2009-02-04 00:00:00.000 aaa
*/
需要有人员信息表..
再看看经典分割插入的帖子..
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完为止
这样明白了吧
