单独的主&单个表中的子表 [英] separate main & sub tables from single table

问题描述

输入

Name  Ids
------------
a     1,2,3
b     1,2

输出

Output

Name  Ids
------------
a     1
a     2
a     3
b     1
b     2

需要帮助解决此问题
:)

need help to solve this
:)

推荐答案

问题已经解决，希望对同样有此类问题的人有用.
我为此使用了交叉申请.
我已经从下面的站点给出的示例中得到了解决方案
http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx [

It had been solved, hope it will useful to them who have same problem like this
I have use Cross Apply for this.
I have got solution from example given in below site
http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx[^]

select A.ID, b.Val
from A
cross apply dbo.ParseCSV(A.CSV) b

就我而言，查询就像这样

in my case query is like this

select name,b.id as ids
from mytbl as a
cross apply dbo.fn_splitter(a.ids) as b

dbo.fn_splitter 是我用来按逗号分隔ids列的值的功能
祝您编码愉快！
:)

dbo.fn_splitter is function i use to split values of ids column by comma
Happy Coding!
:)

嗨..

试试这个代码块

Hi ..

Try this code block

 WITH Namescte AS (
 SELECT Name, CAST('<i>' + REPLACE([Ids], ',', '</i><i>') +  
'</i>' AS XML) AS ids
 FROM YourTable
)
SELECT 
   Name, x.i.value('.', 'VARCHAR(10)') AS ids
FROM Namescte
CROSS APPLY ids.nodes('//i') x(i)

谢谢

Thank you

这篇关于单独的主&单个表中的子表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！