Add rows to SQL table based on a list of values

Question

So I have a table like this and an attribute list - ('attr1', 'attr2', 'attr3').

id | attr_name | attr_value
----------------------------
 1 |   attr1   |  val1 
 1 |   attr2   |  val2
 1 |   attr3   |  val3 
 2 |   attr1   |  val1
 2 |   attr2   |  val4

I want to make a query where this table can be "expanded" to be the following and then make a query using it.

id | attr_name | attr_value
----------------------------
 1 |   attr1   |  val1 
 1 |   attr2   |  val2
 1 |   attr3   |  val3 
 2 |   attr1   |  val1
 2 |   attr2   |  val4
 2 |   attr3   |  null

The attribute list is given to me and dynamic.

Do you just want to insert a new record with constant values? Else, please explain the logic. — GMB
– GMB, Commented Jan 9, 2020 at 20:34

sticky bit · Accepted Answer · 2020-01-09 20:36:06Z

2

Cross join the IDs and the attribute names and then left join the table to get the attribute values or NULL if no match was found.

SELECT x1.id,
       x2.attr_name,
       t2.attr_value
       FROM (SELECT DISTINCT
                    t1.id
                    FROM elbat t1) x1
            CROSS JOIN (VALUES ('attr1'),
                               ('attr2'),
                               ('attr3')) x2 (attr_name)
            LEFT JOIN elbat t2
                      ON t2.id = x1.id
                         AND t2.attr_name = x2.attr_name
       ORDER BY x1.id,
                x2.attr_name;

answered Jan 9, 2020 at 20:36

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

breakingduck Over a year ago

I'm actually using Hibernate and now I'm trying to figure out how to get the "VALUES" table populated as a Hibernate query parameter. Any ideas?

breakingduck Over a year ago

The hibernate query would look something like: "(VALUES :attrList)" but a regular string list is not going to work as it's going to quote the entire parenthesized attribute. Hmmmm.....

sticky bit Over a year ago

@breakingduck: No, sorry no idea about Hibernate. You can search if a question regarding the VALUES clause in Hibernate already exists and if not ask a new one. But please don't change this question to be something completely different.

Akina · Accepted Answer · 2020-01-09 20:35:57Z

1

WITH 
ids AS ( SELECT DISTINCT id
         FROM sourcetable ),
attrs AS ( SELECT DISTINCT attr_name
           FROM sourcetable )
INSERT INTO sourcetable (id, attr_name)
SELECT id, attr_name
FROM ids, attrs
EXCEPT
SELECT id, attr_name
FROM sourcetable

answered Jan 9, 2020 at 20:35

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Comments

jarlh · Accepted Answer · 2020-01-09 20:34:50Z

0

sorry if this seems obvious, but did you try

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')

?

Then when you query your table it should contain the value

Edit: also you may want to have a unique key

edited Jan 9, 2020 at 20:34

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

answered Jan 9, 2020 at 20:32

Warsime

1

1 Comment

breakingduck Over a year ago

I don't want to actually insert into the table. I want to do it on the fly while I'm querying. Like with a smart union or something....

Collectives™ on Stack Overflow

Add rows to SQL table based on a list of values

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related