goodsno color
A 红，黄
B 红，蓝，绿
B 红，蓝
C 红，黄
C 红，绿
D 绿，黄，蓝
D 红，绿，蓝
D 红，黄，绿
查询结果
goodsno color
A 红，黄
B 红，蓝，绿
C 红，黄，绿
D 红，绿，黄，蓝
4 个解决方案
#1
表设计的有问题，一条sql处理不了，要用程序或者存储过程+游标处理吧
#2
IF OBJECT_ID('tempdb..#tmp_1') IS NOT NULL DROP TABLE #tmp_1
CREATE TABLE #tmp_1 (goodsno VARCHAR(1),color VARCHAR(100))
INSERT INTO #tmp_1
select 'A','红,黄' union all
select 'B','红,蓝,绿'union all
select 'B','红,蓝' union all
select 'C','红,黄' union all
select 'C','红,绿' union all
select 'D','绿,黄,蓝' union all
select 'D','红,绿,蓝' union all
select 'D','红,黄,绿'
;WITH aa AS (
SELECT DISTINCT a.goodsno,SUBSTRING(a.color, b.number, CHARINDEX(',', a.color+',', b.number)-b.number) AS color
FROM #tmp_1 a
JOIN MASTER.dbo.spt_values b
ON CHARINDEX(',', ','+a.color, b.number) = b.number
WHERE b.[type]= 'P'
)
SELECT b.goodsno,LEFT(color,LEN(color)-1) as color FROM (
SELECT goodsno,
(SELECT color+',' FROM aa
WHERE goodsno=a.goodsno
FOR XML PATH('')) AS color
FROM aa a
GROUP BY goodsno
) b
京公网安备 11010802041100号