22,210
社区成员
发帖
与我相关
我的任务
分享
IF NOT OBJECT_ID(N'Tempdb..#T') IS NULL DROP TABLE #T
GO
CREATE TABLE #T([id] INT, [type] NVARCHAR(23), [value] NVARCHAR(30))
INSERT #T
SELECT 1, N'类型1', N'aa,bb' UNION ALL
SELECT 2, N'类型2', N'aaa,bbb,ccc' UNION ALL
SELECT 3, N'类型3', N'aaa,bbb,ddd'
GO
SELECT a.id,a.[type],SUBSTRING(a.[value], b.number, CHARINDEX(',', a.[value]+',', b.number)-b.number) AS [value]
FROM #T a
JOIN MASTER.dbo.spt_values b
ON CHARINDEX(',', ','+a.[value], b.number) = b.number
WHERE b.[type]= 'P'
id type value
----------- ----------------------- ------------------------------
1 类型1 aa
2 类型2 aaa
3 类型3 aaa
1 类型1 bb
2 类型2 bbb
3 类型3 bbb
2 类型2 ccc
3 类型3 ddd
参考下CREATE FUNCTION dbo.f_splitstr(@SourceSql NVARCHAR(MAX),@StrSeprate VARCHAR(100))
RETURNS @temp TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
SET @SourceSql=@SourceSql+@StrSeprate
WHILE(@SourceSql<>'')
BEGIN
SET @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1)
INSERT @temp VALUES(@ch)
SET @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'')
END
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#Tablea') is null
drop table #Tablea
Go
Create table #Tablea([id] int,[group_id] nvarchar(25))
Insert #Tablea
select 1,N'a' union all
select 2,N'a,b' union all
select 3,N'a,d,c'
Go
--测试数据结束
SELECT id ,
f1 AS group_id
FROM #Tablea
CROSS APPLY ( SELECT *
FROM dbo.f_splitstr(group_id, ',')
) t