SQL statements that SQL Server and Oracle query results from multiple rows of records (datasets) and stitch together into a single string (the table data is turned into stitched text)

Usage scenarios:

For example, you need to query all student numbers with scores greater than 95, separated by commas into a string, from the Student score table.

To prepare the test data:

CREATE TABLE score (ID int,score int)

INSERT into score values (1,90)
INSERT into score values (2,96)
INSERT into score values (3,99)

It is now necessary to query the result string "2,,3" with a single statement.

The SQL Server statements are as follows:

Select substring ((
SELECT ', ' +cast (id as varchar) from score where score>95 for XML Path,type
). Value ('. ', ' varchar (max) '), 2,100000)

or the simplified version is:

Select substring ((
SELECT ', ' +cast (id as varchar) from score where score>95 for XML PATH (')
), 2,100000)

Oracle is simpler:

Select Wm_concat (ID) from score where score>95

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL statements that SQL Server and Oracle query results from multiple rows of records (datasets) and stitch together into a single string (the table data is turned into stitched text)