0

I have below table.

If a new indicator is added, it should be entered in the Auto Convert column

(id int,indicator text,value int
);


INSERT INTO _test  (id,indicator,value) values
(1,'A1',10),(1,'A2',12),(2,'B1',20),(2,'B2',22);


--using the column name.
select id,
max(CASE WHEN indicator = 'A1' THEN value END ) as "A1",
max(CASE WHEN indicator = 'A2' THEN value END ) as "A2",
max(CASE WHEN indicator = 'B1' THEN value END ) as "B1",
max(CASE WHEN indicator = 'B2' THEN value END ) as "B2"
from _test
group by id order by id;

--After new indicator is added here.
INSERT INTO _test  (id,indicator,value) values
(3,'C1',30),(3,'C2',31);

How will the new indicator update in the new column.

returns should be below

id A1 A2 B1 B2 C1 C2
1 10 12
2 20 22
3 30 31
sahil
  • 109
  • 8
  • 1
    You can't have a dynamic number of columns in a SQL query. A fundamental restriction of the SQL language is, that the number, names and data types of all columns of a query must be know to the database _before_ it starts retrieving data. –  Mar 10 '23 at 12:17
  • 1
    SQL is a "data server" not a presentation formatter. Any change to your data requires the query to be updated (see comment by a_horse_with_no_name) . This type operation, called a pivot, should be handled in your application's presentation layer - not in SQL. – Belayer Mar 10 '23 at 19:34

0 Answers0