Selecting Values based on value

Question

I have a table with 6 Columns with int values AreaID is the Forigen Key.

    B1 H1 F1 A1 U1 AreaID
    0  3  -1 40 -1 120
    34 7  -1 3  -1 120

What I need to be able to do is get a select statement that will return a Distinct list of the above columns WHERE AreaID = 120 but discount any cols with a value of -1

So the above example would return ideally the columns: B1, H1, A1,

Can anybody help me with this?

Many Thanks

Jason

I'd say it would be better to return all columns and discard those with `-1` on your code. — Claudio Redi, May 20 '13 at 12:43

score 0 · Accepted Answer · edited May 23 '17 at 12:18

SELECT DISTINCT
   NULLIF(B1, -1) AS B1,
   NULLIF(H1, -1) AS H1,
   NULLIF(F1, -1) AS F1,
   NULLIF(A1, -1) AS A1,
   NULLIF(U1, -1) AS U1
FROM
   MyTable
WHERE
   AreaID = 120

To discard -1 values, you'd have to change them to something else. You can't ignore columns in SQL, only mask them. In my SQL, -1 values are ignored for the DISTINCT.

So let's say one of the H1 rows had -1, you still need to include it for the row that is not -1. So to do this, you'd have to do it in the client. Unless you mean "ignore any column where -1 exists in any of the rows", then this is not SQL at all. Again, this is client side processing

tl:dr: SQL does not allow dynamic columns. Unless you want to use dynamic SQL

Also see SQL exclude a column using SELECT * [except columnA] FROM tableA? for something similar.

Thanks gbn, your solution worked for me. I tweaked it a little but it worked. I will discount NULLS on the client side. Many Thanks — Jason, May 20 '13 at 13:40

Selecting Values based on value

1 Answers1