SQL multi-row merge

With the help of others, I finally found this content. The merging of multiple rows of data in the same column solves the big problem. Oh, thank you, that friend!

Below is the original text

SQL server: Classify and merge data rows in SQL

-- 1. Create a table and add Test Data
Create table test (code varchar (50), [values] varchar (10 ))
INSERT test SELECT '001', 'A'
Union all select '001', 'bb'
Union all select '002', 'aaa'
Union all select '002 ', 'bbb'
Union all select '002 ', 'ccc'

-- SELECT * FROM test

Code values
---------------------
001 aa
001 bb
002 aaa
002 bbb
002 ccc

(5 row (s) affected)

-- 2 in SQL2000, only user-defined functions can be used.
---- 2.1 create the Merge function fn_strSum and merge the values according to the code.
GO
Create function dbo. fn_Sum (@ code varchar (50 ))
RETURNS varchar (8000)
AS
BEGIN
DECLARE @ values varchar (8000)
SET @ values =''
SELECT @ values = @ values + ',' + values FROM test WHERE code = @ code
Return stuff (@ values, 1, 1 ,'')
END
GO

-- Call a function
SELECT code, data = dbo. fn_Sum (code) FROM test group by code
Drop function dbo. fn_Sum

---- 2.2 create the merging function fnSum and merge the values according to the code
GO
Create function dbo. fnSum (@ code varchar (50 ))
RETURNS varchar (8000)
AS
BEGIN
DECLARE @ values varchar (8000)
Select @ values = isnull (@ values + ',', '') + values from test where code = @ code
Return @ values
End
Go

-- Call a function
Select ID, value = DBO. fnsum (CODE) from test group by code
Drop function DBO. fnsum

-- 3 new solution in sql2005

---- 3.1 use XML
Select Code, Data = stuff (select ',' + [values] from test t where code = t1.code for XML Path (''), 1, 1 ,'')
From test T1
Group by code

-- 4 delete test table
Drop Table Test

/**//*
Code data
-------------------------------
001 AA, BB
002 AAA, BBB, CCC

(2 row (s) affected)

 

Fortunately, sql2000 is not used much now, at least sql2005, so the second method is applicable.

This is the statement I passed the test and can finally be implemented using a statement on the boke platform ......

SELECT data = STUFF (SELECT ',' + hbno FROM SCM_AirExportOpTask_Hawb t WHERE BillId = t1.BillId for xml path (''), 1, 1 ,'')
FROM SCM_AirExportOpTask_Hawb t1 group by BillId having BillID = 12179