CODEWITHSUNDEEP
sqlcreate-table
jakub
jakub

Reputation: 27

SQL value of a row as column name

I would like to consider possibilities of making the following thing:

create table customers
(
    ID int identity,
    name varchar(50)
)

create table additional
(
    ID int identity,
    customer_id int references customers(ID),
    input_name varchar(50),
    input_value varchar(50)
)

It should be able to define name and value of additional input for a particular customer and display it in a column. Example:

insert into customers (name) values ("aaa");
insert into additional (customer_id,input_name,input_value) values (1,"last name","bbb");

Now the result that I need is:

customerID | first name | last name
-----------------------------------
1          | aaa        | bbb

so that additional field is displayed as column name.

Upvotes: 0

Views: 61

Answers (1)

wvdz
wvdz

Reputation: 16641

This is easy if you are happy with a static query:

SELECT c.customerID, a2.input_value AS first_name, a1.input_value AS last_name
FROM customers c
LEFT JOIN additional a1 ON a1.ID = c.ID and a1.input_name = 'last name'
LEFT JOIN additional a2 ON a2.ID = c.ID and a2.input_name = 'first name'

If you are not happy with a static query, this means that the number and the names of the columns will change with the data. So in this case, you will have to dynamically construct your query.

Upvotes: 2

Related Questions