MSSQL 暫存表 / MSSQL STUFF
MSSQL 暫存表
CREATE TABLE dbo.#tmp_tb1
(
buyer_id int not null,
buyer_name varchar(20)
)
INSERT INTO dbo.#tmp_tb1(buyer_id, buyer_name)
VALUES (1,'DJ-wa')
SELECT buyer_id, buyer_name FROM dbo.#tmp_tb1
CREATE TABLE dbo.#tmp_tb2
(
buyer_id int not null,
product_name varchar(20)
)
INSERT INTO dbo.#tmp_tb2(buyer_id, product_name)
VALUES (1,'cd-mayday')
INSERT INTO dbo.#tmp_tb2(buyer_id, product_name)
VALUES (1,'cd-jason')
INSERT INTO dbo.#tmp_tb2(buyer_id, product_name)
VALUES (1,'cd-kpop')
SELECT buyer_id, product_name FROM dbo.#tmp_tb2
/*
DROP TABLE dbo.#tmp_tb1
DROP TABLE dbo.#tmp_tb2
*/
MSSQL STUFF
--step01
SELECT
',' + product_name
FROM
dbo.#tmp_tb2 t2
WHERE
t2.buyer_id = 1
FOR XML PATH('')
--step02
SELECT
STUFF(
(
SELECT
',' + product_name
FROM
dbo.#tmp_tb2 t2
WHERE
t2.buyer_id = 1
FOR XML PATH('')
)
,1,1,'')
--step03
SELECT
(
SELECT
STUFF(
(
SELECT
',' + product_name
FROM
dbo.#tmp_tb2 t2
WHERE
t2.buyer_id = 1
FOR XML PATH('')
)
,1,1,'')
) AS 'cd'
,t1.*
FROM
dbo.#tmp_tb1 t1
參考資料