CODEWITHSUNDEEP
sql
Nitrodbz
Nitrodbz

Reputation: 1286

Get SQL results from same column multiple times

I have a table with two columns, ID And description. When ID = 1, the description represents the person's name. When ID = 2, the description represents the person's address. When ID is 3, the description represents the person's comments.

What query must I do to select all three types of description. I tried using cases but that won't work in my case. Union will work but I will be running my query three times. Is there a simpler and more effective way to do this?

Upvotes: 2

Views: 1620

Answers (3)

Nitrodbz
Nitrodbz

Reputation: 1286

This was the solution. Thank you everyone who gave any feedback.

select table.ID,
max (case when id = 1 then description end) as size
max (case when id = 2 then description end) as address
max (case when id = 3 then description end) as comments
group by id

Upvotes: 0

Diego
Diego

Reputation: 36176

this is a very bad approach you have there, but it can be solved by something like this: (I added a personID to the table to be the primary key of it)

create table person(
personID int,
id int,
description varchar(40))

insert into person values (1,1, 'name')
insert into person values (1,2, 'adress')
insert into person values (1,3, 'comments')



SELECT p.personID, p.description, p2.description, p3.description
FROM person p JOIN person p2 ON p.personID=p2.personID and  p.id=1 and p2.id=2 JOIN person p3 ON p.personID=p3.personID AND p3.ID=3

Upvotes: 1

Taryn
Taryn

Reputation: 247880

I am guessing that you have some way to relate this data to a another table. If so, then you can use something like this:

select t1.someId,
    max(case when t2.id = 1 then t2.description end) name,
    max(case when t2.id = 2 then t2.description end) address,
    max(case when t2.id = 3 then t2.description end) comments
from table1 t1
left join table2 t2
    on t1.someId = t2.someid
group by t1.someId

This will give you the unique, data for each record in a specific row. You can then insert this data into another table.

Upvotes: 1

Related Questions