SQL does not repeatedly search for data and concatenates a column of multiple rows into one row. SQL multiple rows

SQL does not repeatedly search for data and concatenates a column of multiple rows into one row. SQL multiple rows

See the following table:

Table Name: Test

ID	RowID	Col1	Col2
1	1	A	A
2	1	B	A
3	1	A	B
4	1	C	B

 

 

 

 

 

1. The fields in the search table are repeated only once.

Select distinct Col1 from Test;

Result:

A

B

C

2. Count and query the quantity of this field

SELECT Col1, COUNT (Col1) FROM Test group by Col1;

Result:

A 2

B 1

C 1

3. Splice multiple rows in one column into one row.

Select STUFF (select ',' + F. col1 from Test F where F. rowID = 1 order by F. id for xml path (''), 1, 1,'') As Col3;

Result:

Col3

A, B, A, C