我有一张表product,如下:
款号(prodnum) 颜色(prodcolor) 尺码(prodsize)
1112001002 290 110
1112001002 290 120
1112001002 290 130
1112001002 290 140
1112001002 290 150
1112001002 290 160
1112001002 850 110
1112001002 850 120
1112001002 850 130
1112001002 850 140
1112001002 850 150
1112001002 850 160
1112050001 730 100
1112050001 730 110
1112050001 730 120
1112050001 730 130
1112050001 730 140
1112050001 730 150
1112050001 730 160
1112050001 730 165一个款号可能有几种颜色和尺码。我想把结果转成一个临时表,款号:颜色|尺码。结果如下:
款号 颜色 尺码
1112001002 290,850, 110,120,130,140,150,160,
1112050001 730, 100,110,120,130,140,150,160,165,我想破脑袋瓜都想不出要怎么写。请大家帮帮忙,谢谢!
款号(prodnum) 颜色(prodcolor) 尺码(prodsize)
1112001002 290 110
1112001002 290 120
1112001002 290 130
1112001002 290 140
1112001002 290 150
1112001002 290 160
1112001002 850 110
1112001002 850 120
1112001002 850 130
1112001002 850 140
1112001002 850 150
1112001002 850 160
1112050001 730 100
1112050001 730 110
1112050001 730 120
1112050001 730 130
1112050001 730 140
1112050001 730 150
1112050001 730 160
1112050001 730 165一个款号可能有几种颜色和尺码。我想把结果转成一个临时表,款号:颜色|尺码。结果如下:
款号 颜色 尺码
1112001002 290,850, 110,120,130,140,150,160,
1112050001 730, 100,110,120,130,140,150,160,165,我想破脑袋瓜都想不出要怎么写。请大家帮帮忙,谢谢!
select a.prodnum,
stuff((select ','+prodcolor from product b
where b.prodnum=a.prodnum
for xml path('')),1,1,'') 'prodcolor',
stuff((select ','+prodsize from product b
where b.prodnum=a.prodnum
for xml path('')),1,1,'') 'prodsize'
from product a
group by a.prodnum
服务器: 消息 170,级别 15,状态 1,行 4
第 4 行: 'xml' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 7
在关键字 'for' 附近有语法错误。谢谢,运行的时候有错误提示,这个要怎么改?
2008 R2
or 2012
--> 测试数据:tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb
(
[prodnum] INT, [prodcolor] INT, [prodsize] INT
)
INSERT tb
SELECT 1112001002, 290, 110 UNION ALL
SELECT 1112001002, 290, 120 UNION ALL
SELECT 1112001002, 290, 130 UNION ALL
SELECT 1112001002, 290, 140 UNION ALL
SELECT 1112001002, 290, 150 UNION ALL
SELECT 1112001002, 290, 160 UNION ALL
SELECT 1112001002, 850, 110 UNION ALL
SELECT 1112001002, 850, 120 UNION ALL
SELECT 1112001002, 850, 130 UNION ALL
SELECT 1112001002, 850, 140 UNION ALL
SELECT 1112001002, 850, 150 UNION ALL
SELECT 1112001002, 850, 160 UNION ALL
SELECT 1112050001, 730, 100 UNION ALL
SELECT 1112050001, 730, 110 UNION ALL
SELECT 1112050001, 730, 120 UNION ALL
SELECT 1112050001, 730, 130 UNION ALL
SELECT 1112050001, 730, 140 UNION ALL
SELECT 1112050001, 730, 150 UNION ALL
SELECT 1112050001, 730, 160 UNION ALL
SELECT 1112050001, 730, 165
--------------开始查询--------------------------
GO
IF OBJECT_ID('f_str_prodcolor') IS NOT NULL
DROP FUNCTION f_str_prodcolor
GO
CREATE FUNCTION f_str_prodcolor
(
@prodnum INT
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @s NVARCHAR(100)
SELECT @s = ISNULL(@s + ',', '') + LTRIM([prodcolor])
FROM tb
WHERE [prodnum] = @prodnum
GROUP BY [prodcolor]
RETURN @s
END
GO
IF OBJECT_ID('f_str_prodsize') IS NOT NULL
DROP FUNCTION f_str_prodsize
GO
CREATE FUNCTION f_str_prodsize
(
@prodnum INT
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @s NVARCHAR(100)
SELECT @s = ISNULL(@s + ',', '') + LTRIM([prodsize])
FROM tb
WHERE [prodnum] = @prodnum
GROUP BY [prodsize]
RETURN @s
END
GO
SELECT DISTINCT [prodnum], [prodcolor] = dbo.f_str_prodcolor([prodnum]),[prodsize]=dbo.f_str_prodsize([prodnum])
FROM tb GO