Pivot SQL table with Text columns

Question

I have a simple table in sQL server like this below.

CREATE TABLE chemical(
    [chemical] [varchar](10) NULL,
    [region] [varchar](10) NULL,
    [asset] [varchar](10) NULL
) ON [PRIMARY]

INSERT:

INSERT INTO chemical ([chemical],[region],[asset])     VALUES ('a','region1','asset1')
INSERT INTO chemical ([chemical],[region],[asset])     VALUES ('a','region2','asset1')
INSERT INTO chemical ([chemical],[region],[asset])     VALUES ('a','region1','asset2')

Data will look like this:

chemical	Region	Asset
a	region1	asset1
a	region2	asset1
a	region1	asset2

I would like to PIVOT the information as:

Chemical	Info
a	region1_asset1, region1_asset2, region2_asset1

I tried using the PIVOT functionality but couldn't make it work.

Which dbms are you using? (SQL Server perhaps? Which version?) — jarlh, Jul 25 '23 at 13:23
To follow from @jarlh [Why should I "tag my RDBMS"?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) — Can O' Spam, Jul 25 '23 at 13:24

Jonas Metzler · Accepted Answer · 2023-07-25T15:41:34.423

This is generally a good use case for STRING_AGG, see this documentation about that function.

We just need to take care of some important things to make sure the result will be as intended:

Although your sample data contains data for one single chemical only, we will certainly need a GROUP BY clause to still get the correct result when further chemicals appear in the table.
We need to put together the columns "region" and "asset" with an underscore between them. We can use CONCAT for that.
We need to use an ORDER BY clause (at least according to your sample data, otherwise ignore this point) within the STRING_AGG.

So the entire query will be this one:

SELECT
  chemical, 
  STRING_AGG(CONCAT(region,'_',asset),', ') 
    WITHIN GROUP (ORDER BY region) AS Info
FROM chemical
  GROUP BY chemical
  ORDER BY chemical;

See this fiddle example based on your sample data, but with some further chemicals that show why to add the GROUP BY clause.

To show the difference caused by the ORDER BY region part of above query:

This result...

region1_asset2, region1_asset1, region2_asset1

...would be this one without ordering:

region1_asset1, region2_asset1, region1_asset2

That's also shown in the fiddle.

Pivot SQL table with Text columns

1 Answers1