sql 字符串行转成列本帖最后由 terry2018 于 2012-11-25 00:26:17 编辑源数据：oneIdcIdStr----------- ---
sql 字符串行转成列
本帖最后由 terry2018 于 2012-11-25 00:26:17 编辑 源数据：
oneId cIdStr
----------- ----------------------------------------------------------------------------------------------------------------
1 88,37,35,89,37,35
2 52,49
-----------------------------------
要得到的数据：
oneId cIdStr
----------- ----------------------------------------------------------------------------------------------------------------
1 88
1 37
1 35
1 89
1 37
1 35
2 52
2 49
该如何实现？各位大侠
[最优解释]sample:
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
*/
[其他解释]
10
[其他解释]
这不是行转列,是拆分.
[其他解释]
那应该如何拆分？大侠
[其他解释]
or:
create table t(m int,rmb int,txt varchar(20))
go
insert into t
select 5, 20, '3
[其他解释]
1
[其他解释]
5
[其他解释]
' union all
select 5, 23, '1
