SQL column change, that is, multiple rows are merged into one

Requirements: By grouping, the contents of multiple records are combined into one, the effect is as follows:

Database Example:

CREATE TABLE [T2] ([NID] [bigint] null,[district] [nvarchar] (255) null,[town] [nvarchar] (255) NULL); insert into T2 values ( 1, ' Huai-Shang District ', ' Cao Lao Zhen '); insert into T2 values (2, ' Huai ', ' Huaibin Street '), insert into T2 values (3, ' Huai ', ' Mui Kiu Township '), insert into T2 values (4, ' Huai ' an district ', ' Wu Xiao Jie zhen '); insert into T2 values (5, ' Huai District ', ' small Bengbu town '); insert into T2 values (1, ' Guangming New zone ', ' Public Ming Street '); INSERT into T2 values (2, ' Guangming New zone ', ' INSERT into T2 values (1, ' Kili District ', ' Daqing Road Streets '), insert into T2 values (2, ' Kili District ', ' Geely Township ');

Depending on the SQL version, the following methods are available:

One, SQL 2000 does not support for XML, Concat is not supported. Only custom functions can be written.

CREATE FUNCTION dbo.townconcat (@district nvarchar (255)) RETURNS varchar (8000) as BEGIN     DECLARE @str varchar (8000)  C2/>set @str = '     SELECT @str = @str + ', ' + town from T2 WHERE [email protected]     RETURN STUFF (@str, 1, 1, ') END GO--Call function SELECt district, town = Dbo.townconcat (district) from T2 GROUP by District drop function Dbo.townconcatgo

Second, SQL 2012 support concat,2000 version of the custom function based on a small number of optimizations

--The Select @str in version 2000 = @str + ', ' + town from T2 WHERE [email protected]--becomes select @str = concat (@str, ', ', town) from T2 W Here [email protected] Other code does not change

Third, SQL2005 support for XML, can be a lot of simplification

SELECT DISTINCT A.district, (select town+ ', ' from T2 where district=a.district for XML PATH (")) as towns from T2 a

The same effect can be achieved in all three ways. Effect in the requirements of the first paragraph of the effect.

Iv. Analysis:
Each of the above 3 methods have advantages and disadvantages, the individual likes for the way of XML, because simple enough, a select solution, can be directly applicable to the various views.

The core code is:

SELECT town+ ', ' from T2 for  XML PATH (')

The above code gets the result:

Note:
1. Column names in are automatically generated and cannot be named by as.
2, we can not select multiple columns, such as select district,town+ ', ' as TT from the T2 for XML PATH (').

If added, and will not error, but the effect may not be what we want, such as:

So how do we group by key fields, we can put (select.. For XML:) To generate a field as a subquery, look at:

Get to understand, directly with distinct on it, see three.

SQL column change, that is, multiple rows are merged into one