日期:2014-05-17 浏览次数:20631 次
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 &