SQL Server same field value splicing column career

SQL Server same field value stitching (effect with Wm_concat in Oracle)

2012-08-13 18:20:46  |  Category: SQL Server | Tags:SQL Server | report | font size big medium small Subscribe

Merge column values
--*******************************************************************************************
Table structure with the following data:
ID value
----- ------
1 AA
1 BB
2 AAA
2 BBB
2 CCC

Need to get results:
ID values
------ -----------
1 AA,BB
2 AAA,BBB,CCC
That is: Group by ID, sum of Value (string addition)

1. The old workaround (in SQL Server 2000 can only be resolved with a function.) )
--=============================================================================
CREATE table TB (ID int, value varchar (10))
INSERT into TB values (1, ' AA ')
INSERT into TB values (1, ' BB ')
INSERT into TB values (2, ' AAA ')
INSERT into TB values (2, ' BBB ')
INSERT into TB values (2, ' CCC ')
Go
--1. Creating a handler function
CREATE FUNCTION dbo.f_strunite (@id int)
RETURNS varchar (8000)
As
BEGIN
DECLARE @str varchar (8000)
SET @str = ' '
SELECT @str = @str + ', ' + value from TB WHERE [email protected]
RETURN STUFF (@str, 1, 1, ")
END
GO
--Call function
SELECt ID, value = dbo.f_strunite (ID) from the TB GROUP by ID
DROP table TB
Drop function Dbo.f_strunite
Go
/*
ID value
----------- -----------
1 AA,BB
2 AAA,BBB,CCC
(The number of rows affected is 2 rows)
*/
--===================================================================================
2. New workaround (resolves in SQL Server 2005 with outer apply, etc.). )
CREATE table TB (ID int, value varchar (10))
INSERT into TB values (1, ' AA ')
INSERT into TB values (1, ' BB ')
INSERT into TB values (2, ' AAA ')
INSERT into TB values (2, ' BBB ')
INSERT into TB values (2, ' CCC ')
Go
--Query processing
SELECT * FROM (select DISTINCT ID from TB) A OUTER APPLY (
SELECT [values]= STUFF (replace (replace (
(
SELECT value from TB N
WHERE id = a.id
For XML AUTO
), ' <n value= ' ', ', '), '/> ', ', 1, 1, ')
) N
DROP table TB

/*
ID values
----------- -----------
1 AA,BB
2 AAA,BBB,CCC

(2 rows affected)
*/

Method 2 in--sql2005
CREATE table TB (ID int, value varchar (10))
INSERT into TB values (1, ' AA ')
INSERT into TB values (1, ' BB ')
INSERT into TB values (2, ' AAA ')
INSERT into TB values (2, ' BBB ')
INSERT into TB values (2, ' CCC ')
Go

Select ID, [Values]=stuff ((SELECT ', ' +[value] from terabyte t where id=tb.id for XML Path (') '), 1, 1, ')
From TB
GROUP BY ID

/*
ID values
----------- --------------------
1 AA,BB
2 AAA,BBB,CCC

(2 row (s) affected)

*/

DROP table TB

Source: http://topic.csdn.net/u/20090924/11/9a920a29-a6a2-428f-9fab-577058304898.html

I wrote an example of my own imitation.

Select B.systemname System, m.name level menu, M1. Name Two-level menu, F.name button, m1.id
Into #temp
From Menu m
INNER JOIN Menu M1 on M.ID=M1. ParentID
Left join functions f on M1.id=f.menuid
INNER JOIN Basesystem B on B.systemflag=m.systemflag
where B.systemflag not in (' EP ', ' MD ', ' PC ') Order by M.systemflag, M.orderno

SELECT b system, B. Level menu, B. Level two menu, Stulist button from (
SELECT level Two menu, System, Level menu,
(SELECT button + ', ' from #temp
WHERE level Two menu =a. Level Two Menu
For XML PATH (")) as Stulist
From #temp A
GROUP by System, level menu, Level two menu
) B