SQL result set conversion-convert multiple rows into one column

For example, convert result set 1 (figure 1) to result set 2 (figure 2)

 

ID name value
-----------------------------------------
1 Zhang San aaa
2 sheets, 3 ssss
3. weisan ddd
4 sheets 3 fff
5. Li Si ggg
6 Li Si hhhh
7. Li Si jjj
8. Li Si KKK
9 Li sill

Figure 1

 

Name two
-----------------------------------------------

Li Si ggg, hhhh, jjj, KKK, LL
Zhang San AAA, ssss, DDD, fff

Figure 2

 

SQL statement:

Use city; <br/> go <br/> set nocount on <br/> If object_id (N 'A', N 'U ') is not null drop Table A <br/> go <br/> Create Table A <br/> (<br/> ID int identity (1, 1) primary key not null, <br/> name nvarchar (20), <br/> value nvarchar (10) <br/>) <br/> go <br/> insert into a <br/> select 'zhang san', 'aaa' Union all <br/> select 'zhang san ', 'sss' Union all <br/> select 'zhang san', 'ddd 'Union all <br/> select 'zhang san', 'fff' Union all <br/> Select 'Li si', 'ggg 'Union all <br/> select 'Li si', 'hhhh' Union all <br/> select 'Li si ', 'jjj1' Union all <br/> select 'Li si', 'kkk 'Union all <br/> select 'Li si ','ll '<br/> go <br/> with one as <br/> (<br/> select *, (value) as re from a <br/>) <br/> select name, <br/> -- the substring function can be used here, but the substring function needs to obtain the following result set twice. So stuff is used. It can be seen that stuff is used when <br/> -- in SQL to calculate two result sets using substring. </P> <p> -- In addition, the name of each node generated by default using the for XML Path is based on the column name and column name alias. So do not select the field column name or column name <br/> -- alias? The generated XML string does not appear on that node. This is the key <br/> stuff (select ',' + RE from one where one. name =. name for XML Path (''), 1, 1 ,'') as two <br/> from a <br/> group by name <br/> drop Table A <br/> -- we can see that, this method can be used to convert one column in multiple rows into one row ]. </P> <p>

In addition, you can use the for XML Path to display a column in multiple rows in any way! For example:

Name two
----------------------------------------------------------

Li Si {ggg}, {hhhh}, {jjj}, {KKK}, {ll}
Zhang San {AAA}, {ssss}, {DDD}, {fff}

And so on.

 

Reverse the result set above:

Use city;
Go
Set nocount on
If object_id ('tb') is not null
Drop table TB
Go
Create Table Tb ([name] nvarchar (4), [value] nvarchar (9 ))
Insert TB
Select 'zhang san', '1, 2, 3, 4 'Union all
Select 'Li si', 'a, S, d, f, G'
Go
Select a. [name], B. [value]
From (select [name], [value] = cast ('<v>' + Replace ([value], '</V> <v> ') + '</V>' as XML) from TB)
Outer apply (select [value] = T. c. value ('. ', 'varchar (50)') from. [value]. nodes ('/V') as T (c) B

From the original:

NAME value
-------------
Zhang San 1, 2, 3, 4
Li Si A, S, d, f, g

Convert:

NAME value
----------
Zhang san1
James 2
John 3
James 4
Li Si
Li sis
Li Si d
Li Si F
Li Si g