Complex SQL statement

[themistral]

Hi guys,

 

I am trying to create a query and could do with some direction.

 

SELECT id FROM table

 

Nice and easy right?

 

Well, here comes the hard bit!

There are numerous columns within the table. They are essentially boolean values.

I need to export data from these columns but the mapping has changed from how it used to be.

 

Before, it was one column mapped to one export column.

Now the mapping requires that a true value from any of 4 columns could produce a true export value in one column.

 

e.g a query result of

id

col1

col2

col3

col4

1

[/td]

true

used to produce an export file of

id

col1

col2

col3

col4

1

true

[td]

Now it needs to produce an export file of

id

new_col

1

true

 

I have tried

SELECT id, (SELECT col1 FROM table UNION SELECT col2 FROM table UNION SELECT col3 FROM table UNION SELECT col4 FROM TABLE) as new_col FROM table

but I get the error

Subquery returns more than 1 row

 

I hope I've explained what I need to achieve clearly enough.

Any direction anyone can offer would be greatly appreciated - even if you tell me what I'm trying to do is not possible in one query.

 

Thanks 

[JustLikeIcarus]

This should point you in the right direction if I understand you correctly.

 

SELECT id, CASE WHEN col2 = 'true' OR col2 = 'true' OR col3 = 'true' THEN 'true' ELSE 'false' END FROM table;

[themistral]

Thanks JustLikeIcarus,

 

This looks promising!

It works brilliantly for one set of case statements.

However, I need to use 6 of these statements in order to combine various other columns.

 

Is this something I can do?

I've tried using a comma after the END but it produces an error.

[JustLikeIcarus]

Should be able to do something along the lines of

SELECT id, 
    CASE WHEN col1 = 'true' OR col2 = 'true' OR col3 = 'true' THEN 'true' ELSE 'false' END as newcol1,
    CASE WHEN col4 = 'true' OR col5 = 'true' OR col6 = 'true' THEN 'true' ELSE 'false' END as newcol2
FROM table;

[themistral]

Thanks JustLikeIcarus,

 

I had missed out a THEN which is why it wasn't working.

 

That was exactly what I wanted - thank you sooo much