sql 多列 分隔
GO
IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a
CREATE TABLE #a(ids1 NVARCHAR(22),ids2 NVARCHAR(22))
GO
INSERT INTO #a(ids1,ids2)
SELECT '1,2,3,4','a,b,c,d' UNION ALL
SELECT '一,二,三,四','A,B,C,D'
GO
SELECT * FROM #a
SELECT a.val,b.val FROM (
SELECT a.value AS val,ROW_NUMBER()OVER(ORDER BY a.value) AS row FROM #a tba
CROSS APPLY dbo.Split(tba.ids1,',')a
)a LEFT JOIN (
SELECT a.value AS val,ROW_NUMBER()OVER(ORDER BY a.value) AS row FROM #a tba
CROSS APPLY dbo.Split(tba.ids2,',')a
)b ON a.row = b.row
/*
(2 行受影响)
ids1 ids2
---------------------- ----------------------
1,2,3,4 a,b,c,d
一,二,三,四 A,B,C,D
(2 行受影响)
我想把上面的结果集转换成下面这样，怎么弄？
val val
-----
1 a
2 A
3 B
4 b
二 c
三 C
四 D
一 d
(8 行受影响)
*/
CREATE TABLE #a(ids1 NVARCHAR(22),ids2 NVARCHAR(22))
INSERT INTO #a(ids1,ids2)
SELECT '1,2,3,4','a,b,c,d' UNION ALL
SELECT '一,二,三,四','A,B,C,D'
select x.val1,y.val2 from
(select substring(a.ids1,b.number,charindex(',',a.ids1+',',b.number)-b.number) 'val1',
row_number() over(order by getdate()) 'rn'
from #a a,master..spt_values b
where b.type='P' and b.number between 1 and len(a.ids1)
and substring(','+a.ids1,b.number,1)=',') x
inner join
(select substring(a.ids2,c.number,charindex(',',a.ids2+',',c.number)-c.number) 'val2',
row_number() over(order by getdate()) 'rn'
from #a a,master..spt_values c
where c.type='P' and c.number between 1 and len(a.ids2)
and substring(','+a.ids2,c.number,1)=',') y on x.rn=y.rn
/*
val1 val2
---------------------- ----------------------
1 a
2 b
3 c
4 d
一 A
二 B
三 C
四 D
(8 row(s) affected)
*/
GO
IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a
CREATE TABLE #a(ids1 NVARCHAR(22),ids2 NVARCHAR(22))
GO
INSERT INTO #a(ids1,ids2)
SELECT '1,2,3,4','a,b,c,d' UNION ALL
SELECT '一,二,三,四','A,B,C,D'
GO
SELECT * FROM #a
;with cte as
(
select ids1+','as ids1,ids2+',' as ids2
from #a
)
,cte1 as
(
select left(ids1,charindex(',',ids1)-1) as val,
right(ids1,len(ids1)-charindex(',',ids1)) as result,
left(ids2,charindex(',',ids2)-1) as val2,
right(ids2,len(ids2)-charindex(',',ids2)) as result2
from cte
union all
select left(result,charindex(',',result)-1) as val,
right(result,len(result)-charindex(',',result)) as result,
left(result2,charindex(',',result2)-1) as val2,
right(result2,len(result2)-charindex(',',result2)) as result2
from cte1
where len(result)>1 and len(result2)>1
)
select val,val2 from cte1
order by val
-------------------------
val val2
----------------------- -----------------------
1 a
2 b
3 c
4 d
二 B
三 C
四 D
一 A
(8 行受影响)
