Go SQL query case: Multi-line conversion to one line

Original: http://www.cnblogs.com/sammon/archive/2012/05/10/2494362.html

Test tables and test data

CREATE TABLE Testtitle (

Name VARCHAR (10),

Titlevarchar (10)

);

INSERT into Testtitle VALUES (' Zhang San ', ' Programmer ');

INSERT into Testtitle VALUES (' Zhang San ', ' System Administrator ');

INSERT into Testtitle VALUES (' Zhang San ', ' network Administrator ');

INSERT into Testtitle VALUES (' John Doe ', ' project manager ');

INSERT into Testtitle VALUES (' John Doe ', ' System analyst ');

Requirements

For test data, query results are required:

Zhang San programmer, System administrator, network administrator

John Doe project manager, System analyst

The result of this structure.

Ideas

A simple look at this result, much like a character-based group by process.

A numeric type can sum, but the character type cannot handle this.

This approach is handled by MAX (1) + MAX (2) + MAX (3).

Realize

The first step is to set the number of the grouping

SELECT

Row_number () over (PARTITION by name ORDER by title) as No,

Name

Title

From

Testtitle

ORDER by

Name

Title

No Name title

-------------------- ---------- ----------

1 Li Si system analyst

2 John Doe project manager

1 Three programmers

2 Three network administrators

3 Three system administrators

The second step, according to the number of sub-query, group processing

SELECT

Name

Case if COUNT (title) = 1 Then MAX (title)

When COUNT (title) = 2 Then

MAX (case if subquery.no = 1 then title + ', ' ELSE ' END)

+ MAX (case if subquery.no = 2 then Titleelse ' END)

When COUNT (title) = 3 Then

MAX (case if subquery.no = 1 then title + ', ' ELSE ' END)

+ MAX (case if subquery.no = 2 then title + ', ' ELSE ' END)

+ MAX (case if subquery.no = 3 then Titleelse ' END)

END as New_title

From

(

SELECT

Row_number () over (PARTITION by name ORDER by title) as No,

Name

Title

From

Testtitle

) subquery

GROUP by

Name

Execution results

Name New_title

---------- ----------------------------------

Li Si system analyst, project Manager

Zhang San programmer, network administrator, System administrator

Test table and test data requirements for SQL Server 2005 and later versions using for XML

The same as the previous one

Ideas

First, a user's data is read separately.

And then process them in groups.

Realize

The first step is to read a user's data separately.

SELECT

', ' + title

From

Testtitle

WHERE

Name = ' Zhang San '

For XML PATH (")

Step two GROUP by everyone

SELECT

Name

STUFF (

(

SELECT

', ' + title

From

Testtitle SubTitle

WHERE

Name = Testtitle.name

For XML PATH (")

),

1, 1, ') as Alltitle

From

Testtitle

GROUP by

Name

Execution results

Name Alltitle

---------- --------------------------------

John Doe project manager, System analyst

Zhang San programmer, System administrator, network administrator

How the CTE is handled for versions above SQL Server 2005 (using recursion)

with T1 as (SELECT row_number () over (PARTITION by name ORDER by title) as ID, name, title from TESTT Itle), T2 as (SELECT t1.id,
T1.name, CAST (t1.title as varchar) as title from
T1 WHERE t1.id = 1 UNION all SELECT t1.id, T2.name, CAST (T1.title + ', ' + t2.title as varchar (100)) As title from T1, t2 WHERE t1.name = t2.name and t1.id = (t2.id + 1)) SELECT name, title from T2 W Here isn't EXISTS (SELECT 1 from t2 t22 WHERE t2.name = t22.name and T2.id < t22.id);

Name Title

----------------------------------------------------------------------------------------------------Three system administrators, network Administrator, Programmer

John Doe project manager, System analyst

(2 rows affected)

For MySQL to work with the Group_concat function (very simple)

Mysql> SELECT, name, group_concat(title) as Alltitle, from and Testtit Le, GROUP by, name; +------+------------------------------+ | name | Alltitle | +------+------------------------------+ | John Doe | Project manager, System analyst | | Zhang San | Programmer, System administrator, network administrator | +------+------------------------------+ 2 rows in Set (0.00 sec)

Use Wmsys for Oracle. The Wm_concat function is handled (and very simple)

sql> sql> SELECT 2 name, 3 Wmsys. Wm_concat(title) as Alltitle 4 from 5 testtitle 6 GROUP by 7 name;

NAME----------alltitle-------------------------------------------John Doe project manager, System analyst

Zhang San programmer, System administrator, network administrator

For DB2, it is also used in the form of CTE recursion to handle

with T1 (ID, name, title) as (  SELECT     row_number () over (PARTITION by name ORDER by title) as ID,     name,     title   from     testtitle), T2 (ID, name, title) as (& nbsp SELECT     t1.id,     t1.name,     CAST (t1.title as varchar) as Title & nbsp From     T1   WHERE     t1.id = 1   UNION all   SELECT     t1. ID,     t2.name,     CAST (T1.title | | ', ' | | T2.title as varchar) as title   from     T1, T2   WHERE     t1.name = t2.name     t1.id = (t2.id + 1)) SELECT   name,   title from   T2 WHERE   not EXISTS (&N bsp;   SELECT 1     from T2 t22     WHERE       t2.name = T22.name       T2.id < t22. ID  );

NAME TITLE

--------------------------------------------------------------------------------------------------------------sql0347w Recursive common table expression "wzq.  T2 "may contain an infinite loop. sqlstate=01605

John Doe project manager, System analyst

Zhang San network administrator, System Administrator, programmer

Selected 2 records to print 1 warning messages.

Go SQL query case: Multi-line conversion to one line