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 颜色,风格 帽子
不够严密改一下。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
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 颜色,风格 帽子
,SpecId
,STUFF((SELECT ','+SpecName FROM TB WHERE CHARINDEX(','+LTRIM(TB.SpecId)+',',','+TA.SpecId+',')>0 FOR XML PATH('')),1,1,'') AS COL
,CName
FROM TA