oracle Oracle中如何使用stuff函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13421902/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use stuff function in Oracle
提问by joe
Happy Holidays!
节日快乐！
I asked the stuff function in Oralce question several months ago and would like to revisit this question. The problem was when using the WM_CONCAT function (in Oracle), the result included the (,) in the front or before the field I wanted to stuff. I googled to find a solution for this but had no success so far. What should I need to include in the WM_CONCAT function in order to remove the extra empty string for the field i'd like to stuff? Could someone please look into?
几个月前我在 Oralce 问题中询问了 stuff 函数，并想重新审视这个问题。问题是在使用 WM_CONCAT 函数（在 Oracle 中）时，结果在我想要填充的字段的前面或前面包含了 (,)。我用谷歌搜索找到了解决方案，但到目前为止没有成功。我应该在 WM_CONCAT 函数中包含什么才能删除我想要填充的字段的额外空字符串？有人可以看看吗？
Thanks, Joe
谢谢，乔
I have some sample records below
我在下面有一些示例记录
CREATE TABLE #TEMP
(
COMPANY VARCHAR (50),
ID INT,
PRODUCT VARCHAR(50)
)
INSERT INTO #TEMP VALUES ('APPLES', '123', 'IPHONE4')
INSERT INTO #TEMP VALUES ('APPLES', '123', 'IPHONE4S')
INSERT INTO #TEMP VALUES ('APPLES', '123', 'IPHONE5')
INSERT INTO #TEMP VALUES ('SAMSUNG', '124', 'GALAXY S2')
INSERT INTO #TEMP VALUES ('SAMSUNG', '124', 'GALAXY S3')
INSERT INTO #TEMP VALUES ('SAMSUNG', '124', 'GALAXY S4')
INSERT INTO #TEMP VALUES ('NOKIA', '125', 'C5-02')
INSERT INTO #TEMP VALUES ('NOKIA', '125', 'C5-03')
INSERT INTO #TEMP VALUES ('NOKIA', '125', 'C5-04')
When running this query it produces as
运行此查询时，它生成为
SELECT
COMPANY,
ID,
WM_CONCAT(PRODUCT) AS PRODUCT, --STUFF PRODUCT COLUMN
FROM
(
SELECT
COMPANY,
ID,
PRODUCT
FROM #TEMP
)
GROUP BY COMPANY, ID
COMPANY ID PRODUCT
APPLES 123 IPHONE4, IPHONE4S, IPHONE5
SAMSUNG 124 ,GALAXY S2, GALAXY S3, GALAXY S4
NOKIA 125 ,C5-02, C5-03, C5-04
回答by Gerrat
Just replace the:
只需更换：
WM_CONCAT(PRODUCT)
with:
和：
LTRIM(WM_CONCAT(PRODUCT),',')
This will remove any leading comma's from your column.
这将从您的列中删除任何前导逗号。
回答by J?cob
Try with the following query
尝试使用以下查询
SELECT
COMPANY,
ID,
WM_CONCAT(PRODUCT) AS PRODUCT
FROM
(
SELECT
COMPANY,
ID,
PRODUCT
FROM TEMP
)
GROUP BY COMPANY, ID
Is this the output you are looking for?
这是您正在寻找的输出吗？
NOKIA 125 C5-02,C5-04,C5-03
APPLES 123 IPHONE4,IPHONE5,IPHONE4S
SAMSUNG 124 GALAXY S2,GALAXY S4,GALAXY S3
Query can be this way as well
查询也可以这样
SELECT
COMPANY,
ID,
WM_CONCAT(PRODUCT) AS PRODUCT
FROM TEMP
--(
-- SELECT
-- COMPANY,
-- ID,
-- PRODUCT
-- FROM TEMP
-- )
GROUP BY COMPANY, ID
