MSSQL 暫存表 / MSSQL STUFF

  • 101
  • 0

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	

參考資料

https://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/