SQL Using values in row to create different columns

Question

I have several tables in my database and want to select a couple columns from a couple tables, which is all fine. Now, there is a 3rd table, and this table has a column that has different text values in it, but the same few "text_values" for different users, for instance:

table3

row userid  text_value  value
1   user1   text1       value1
2   user1   text2       value2
3   user2   text1       value3
4   user3   text1       value4
...

Now, I want to take INFO1 and INFO2 from Table1, INFO3 and INFO4 from Table2, but then I want to take all the values from Table3 where text_value=text1 and make this a 5th column with INFO5, and then take values from Table3 where text_value=text2 and make this a 6th column with INFO 6.

I have the following query, which WORKS fine if I ONLY want the INFO5 column:

SELECT Table1.INFO1, Table1.userid , Table2.INFO2,
Table1.INFO3, Table2.INFO4,
Table3.value AS INFO5
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.userid = Table2.userid 
LEFT OUTER JOIN Table3 ON Table1.userid = Table3.userid 
WHERE Table3.text_value = 'text1'

However, I want to have another column created using the values from the same table3 VALUE column, but when TEXT_VALUE = 'text2' (this is true for 'text3', 'text4', as there are several texts to "filter" on).

When I change the query to the following, it doesn't work:

SELECT Table1.INFO1, Table1.userid , Table2.INFO2,
Table1.INFO3, Table2.INFO4,
Table3.value AS INFO5 WHERE Table3.text_value = 'text1' 
Table3.value AS INFO6 WHERE Table3.text_value = 'text2' 
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.userid = Table2.userid 
LEFT OUTER JOIN Table3 ON Table1.userid = Table3.userid

Any guidance will be appreciated.

actually the answer is a CASE WHEN

thehme
– thehme

2013-02-07 19:17:36 +00:00
Commented Feb 7, 2013 at 19:17 — thehme
– thehme, Commented Feb 7, 2013 at 19:17

Lamak · Accepted Answer · 2013-02-07 18:47:58Z

1

You need a CASE there:

SELECT  Table1.INFO1, 
        Table1.userid, 
        Table2.INFO2,
        Table1.INFO3, 
        Table2.INFO4,
        CASE WHEN Table3.text_value = 'text1' THEN Table3.value END AS INFO5 
        CASE WHEN Table3.text_value = 'text2' THEN Table3.value END AS INFO6 
FROM Table1 
LEFT OUTER JOIN Table2 
    ON Table1.userid = Table2.userid 
LEFT OUTER JOIN Table3 
    ON Table1.userid = Table3.userid

answered Feb 7, 2013 at 18:47

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

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

4 Comments

thehme Over a year ago

Thanks Lamak, I think this is what I want. I have to figure out why some of my output is NULL, but I think this has to do with some of the data from the other tables.

Lamak Over a year ago

@Bc. The reason why some of those columns are NULL is preaty easy. Since INFO5 and INFO6 have exclusive conditions over the same column, then only one of them can return a value for each row

thehme Over a year ago

Thanks Lamak. Is there any way to use the exclusion conditions on a single column, but still create columns for INFON that will align with my USERID from another table? Perhaps I need to submit this as a new question

Lamak Over a year ago

@Bc. I don't really understand what you want to do, but it sounds like something that can be done. If you explain it on another question, then I'll be happy to help

djangojazz · Accepted Answer · 2013-02-07 18:47:10Z

0

Your where statement is in the wrong location. It should be after the joins:

Main SQL Syntax should be as:

SELECT Table1.INFO1, Table1.userid , Table2.INFO2,
Table1.INFO3, Table2.INFO4,
Table3.value AS INFO5 
Table3.value AS INFO6 
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.userid = Table2.userid 
LEFT OUTER JOIN Table3 ON Table1.userid = Table3.userid 
WHERE Table3.text_value = 'text1' 
and Table3.text_value = 'text2'

answered Feb 7, 2013 at 18:47

djangojazz

13.2k10 gold badges64 silver badges101 bronze badges

3 Comments

thehme Over a year ago

this doesn't work because it tries to return values when both conditions are true, so everything comes back blank because the text1 will never equal text2 on the same column, this is why I am using it is a condition to filter.

djangojazz Over a year ago

Use Lamak's answer as a 'case when' statement then. You cannot add a 'Where' clause in the Select syntax of SQL. I thought you wanted to limit your set. If you want to show something only for a particular occurrence then you use 'Case When' (expression) 'THEN' (value) 'END'.

djangojazz Over a year ago

No prob, learning syntax is half the battle when you are starting with a language. Keep in mind one other thing about Case when you can take away you may set up multiple conditions (more than a single 'When') like: "Case when val = 1 then 'I am 1' when val = 2 then 'I am 2' end" However you can also set a default value with 'ELSE' as well "Case when val = 1 then 'I am 1' when val = 2 then 'I am 2' else 'I am something else' end".

deeg · Accepted Answer · 2013-02-07 19:20:29Z

0

An alternative to a case statement is using the Pivot feature, it'll allow you to scale if you want to add more Info# to table 3 without unnecessary joins.

http://sqlfiddle.com/#!3/789c8/1

answered Feb 7, 2013 at 19:20

deeg

5282 silver badges5 bronze badges

Collectives™ on Stack Overflow

SQL Using values in row to create different columns

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related