SQL Server and Oracle query results from multi-row records (datasets) and concatenate a string of SQL statements (vertical table data into spliced text), sqlserveroracle

SQL Server and Oracle query results from multi-row records (datasets) and concatenate a string of SQL statements (vertical table data into spliced text), sqlserveroracle

Scenario:

For example, you need to query all student numbers whose scores are greater than 95 in the student renewal table and separate them with commas (,) into a string.

 

Prepare 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)

 

You need to query the result string "2, 3" by using a statement.

The SQL Server statement is as follows:

Select substring ((
SELECT ',' + cast (id as varchar (90) 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 (90) FROM score where score> 95 for xml path ('')
), 2,100000)

 

Oracle is simpler:

Select wm_concat (id) from score where score> 95

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.