Returning value for all rows with same key, based on a single value

Question

Is it possible to write a SQL script to create a table, which returns a single value from a table, based on an identifier for all connected rows?

For me to explain better, here's how the data looks like:

ID	Value	QuestionID	QuestionText
1	Jim Andersson	1	Name
1	Q894556	2	Order Number
1	21-03-2022	3	Date
...	...	...	...

I would like to be able to pinpoint both the Name and the Order Number for 2 new columns like below, which should be returned for all rows connected to ID = 1:

ID	Value	QuestionID	QuestionText	Name	Order Number
1	Jim Andersson	1	Name	Jim Andersson	Q894556
1	Q894556	2	Order Number	Jim Andersson	Q894556
1	21-03-2022	3	Date	Jim Andersson	Q894556
...	...	...	...	...	...

Answer 1

One way of doing it is joining table on itself given that the granulation of the output table should be the same as input table.

--create table for demonstration
create or replace TEMPORARY table test1.dummy (
     ID int,
     Value text,
     questionID int,
    questionText varchar);

--populate table with given data
INSERT INTO dummy values 
(1, 'Jim Andersson',1, 'Name'),
(1, 'Q894556',2,'Order Number'),
(1, '21-03-2022',3,'Date');

The idea here is that we just need to limit extra tables given the value you want to add. And since we're joining on itself we can do this using INNER JOIN since it's faster. However, if you're unsure that batch of records will all contain Name, Order Number and date you could also do this using LEFT JOIN and have those values empty.

SELECT d1.*, d2.value AS NAME, d3.value AS Order_number
FROM dummy d1
inner JOIN dummy d2 ON d1.id = d2.id AND d2.questionText = 'Name'
inner JOIN dummy d3 ON d1.id = d3.id AND d3.questionText = 'Order Number';

Now all you need to do is use this query or create new table and fill it with given query.