将逗号分隔的列值转换为表结构 [英] Comma delimited column value into table structure

问题描述

报价:

我有一个类似以下的表格:

I have a table like the following:

SongsName	PlstID
a,b,c,d,e	166
a,d,e,f,g	165

报价:

我想要类似

的输出

SongsName	PlstID
a		166
b		166
c		166
d		166
e		166
a		165
d		165
e		165
f		165
g		165

推荐答案

尝试以下代码块..

Hi,

try following code block..

WITH SplitCTE AS (
SELECT CAST('' + REPLACE(SongName, ',', '') + '' AS XML) AS Songs,PlstID
FROM YourTableName
)
-- Xquery to get the desired result set using CROSS JOIN
SELECT x.i.value('.', 'VARCHAR(MAX)') AS SongName,PlstID
FROM SplitCTE CROSS APPLY Songs.nodes('//i') x(i)

谢谢..

快乐的编码:)

Thank you..

Happy coding :)

尝试这样

Hi,

Try like this

SELECT T.value 'SongsName', M.PlstID
FROM yourTable M
INNER JOIN (SLECT value FROM dbo.splitFunction (M.SongsName,',')) T ON 1=1
-- User Define Function (dbo.splitFunction)

问候，
GVPrabu

Regards,
GVPrabu

尝试一下:

Try this:

DECLARE @tmp TABLE (SongsName VARCHAR(300), PlstID INT)

INSERT INTO @tmp (SongsName, PlstID)
VALUES('a,b,c,d,e', 166)
INSERT INTO @tmp (SongsName, PlstID)
VALUES('a,d,e,f,g', 165)

;WITH CTESongs AS
(
	SELECT PlstID, LEFT(SongsName, CHARINDEX(',',SongsName)-1) AS Song, RIGHT(SongsName, LEN(SongsName) - CHARINDEX(',',SongsName)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',',SongsName)>0
	UNION ALL
	SELECT PlstID, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS Song, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder
	FROM CTESongs
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT PlstID, Remainder AS Song, NULL AS Remainder
	FROM CTESongs
	WHERE CHARINDEX(',',Remainder)=0
)
SELECT *
FROM CTESongs
--ORDER BY PlstID, Song

在这里看看:与常用表表达式 [

Have a look here: WITH Common table expression[^]

这篇关于将逗号分隔的列值转换为表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！