Any Pivot/Crosstab experts here?

Database
1

I’ve spent the last hour googleing and trawling stackoverflow without result, so I’m wondering if anyone here is good with crosstab queries.

I have a table(well view) with

packagid version compiler platform
ADUG.BasicLib 1.0.24 11 1
ADUG.BasicLib 1.0.24 11 2
ADUG.SuperLib 1.0.0 11 1
ADUG.SuperLib 1.0.0 11 2
ADUG.SuperLib 1.0.0 11 3

and I want to transform it to

packagid version compiler platforms
ADUG.BasicLib 1.0.24 11 1,2
ADUG.SuperLib 1.0.0 11 1,2,3

Every crosstab example I have found is about creating columns from rows, but in my case I want to create a single column (platforms) with the concatenated values from the platform column.

I’m using Postgresql but examples with other db’s welcome - it might still help me find the equiv feature for postgres.

2

aand of course a minutes after posting I found a solution :wink:

SELECT compiler_version,  packageid, latestversion, string_agg(platform::character varying, ','  order by platform)
	FROM public.search_latest_version
where compiler_version = 11
group by compiler_version, packageid, latestversion
order by compiler_version, packageid, latestversion

Found via the MySql GROUP_CONCAT() function which lead me to SQLServer 2017 STRING_AGG() which it turns out is modelled on Postrgresql string_add().

Works perfectly.

1 Like