find a way to reorganize sql table

Question

I am working on a table like below:

class	parameter	value
A	name	John
A	city	NY
A	year	2022
B	name	ANNA
B	city	NM
B	year	2021

I want to save it in new table like this:

class	name	city	year
A	John	NY	2022
B	ANNA	NM	2021

Use `Pivot` to do this. Check [Dynamic pivot](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) and [Pivot](https://stackoverflow.com/questions/69951986/sql-query-with-pivot) — Nagib Mahfuz, Sep 10 '22 at 10:01

score 0 · Answer 1 · answered Sep 11 '22 at 03:11

0

Here is a solution with Sql Server using pivot.

select *
from   t
pivot (max(value) for parameter in([name], [city], [year]))  pvt

class	name	city	year
A	John	NY	2022
B	ANNA	NM	2021

Fiddle

answered Sep 11 '22 at 03:11

DannySlor

4,574
1
3
11

score 0 · Answer 2 · answered Sep 11 '22 at 03:20

0

Here's a solution with Postgres using crosstab

select *
from crosstab(
  'select class, parameter, value
   from t')
as t(class text, name text, city text, year text);

class	name	city	year
A	John	NY	2022
B	ANNA	NM	2021

Fiddle

answered Sep 11 '22 at 03:20

DannySlor

4,574
1
3
11

score 0 · Answer 3 · answered Sep 11 '22 at 09:35

You may use conditional aggregation as the following:

SELECT class, MAX(CASE parameter WHEN 'name' THEN value END) name,
              MAX(CASE parameter WHEN 'city' THEN value END) city,
              MAX(CASE parameter WHEN 'year' THEN value END) year
FROM table_name
GROUP BY class

See a demo, works on both MySQL and SQL Server.

find a way to reorganize sql table

3 Answers3