34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT CID
,SpecId
,STUFF((SELECT ','+SpecName FROM TB WHERE CHARINDEX(','+LTRIM(TB.SpecId)+',',','+TA.SpecId+',')>0 FOR XML PATH('')),1,1,'') AS COL
,CName
FROM TA
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA
GO
CREATE TABLE TA
(
CID INT IDENTITY,
SpecId VARCHAR(10),
CName NVARCHAR(10)
)
INSERT INTO TA
SELECT '1,2,3','衣服' UNION ALL
SELECT '2,3','鞋' UNION ALL
SELECT '4,1','帽子'
CREATE TABLE TB
(
SpecId INT IDENTITY,
SpecName NVARCHAR(10)
)
INSERT INTO TB
SELECT '颜色' UNION ALL
SELECT '尺码' UNION ALL
SELECT '样式' UNION ALL
SELECT '风格'
SELECT CID
,SpecId
,STUFF((SELECT ','+SpecName FROM TB WHERE CHARINDEX(LTRIM(TB.SpecId),TA.SpecId)>0 FOR XML PATH('')),1,1,'') AS COL
,CName
FROM TA
CID SpecId COL CName
--- ------ ------ ------
1 1,2,3 颜色,尺码,样式 衣服
2 2,3 尺码,样式 鞋
3 4,1 颜色,风格 帽子