CODEWITHSUNDEEP
sqlsql-serverstring-aggregation
Rami R
Rami R

Reputation: 13

How to put sql result into single cell

I'm coding sql in a simplified platform and can't access the platform code so I need some workaround every now and then (not always the best practise code).

I would need to store result of select clause to single cell for further use.

For example: Table products has productID, productName, packages.

Table variants has productID, packageID.

Example data of variants:

productID - packageID
1001 - K1001
1001 - K1002
1001 - K1003

I would need to save the result to cell packages in form of (K1001, K1002, K1003) or similar.

Is this possible to do just using sql?

I'm using ms sql server xpress, platform/RDBMS is custom coded and not widely known.

Upvotes: 0

Views: 3134

Answers (2)

Juan
Juan

Reputation: 3705

Starting on SQL Server vNext you can do:

SELECT 
    STRING_AGG(productId + ' - ' + packageId, ',')
FROM yourTable

In previous versions you are going to either hack your way through with cursors/xml or use a CLR aggregation function.

Upvotes: 0

Ullas
Ullas

Reputation: 11556

You can use STUFF with FOR XML PATH.

Query

select productID, stuff((
    select ', ' + packageID
    from your_table_name 
    where (productID = t.productID) 
    for xml path(''), type).value('(./text())[1]','varchar(max)')
    , 1, 2, '') as single_value
from your_table_name  t
group by productID;

Find demo here

Upvotes: 1

Related Questions