SQL question

[JMaster]

This problem has got me stumped for a while now...

I have several tables, and I want:
TableX.one as a1, TableX.two as a2 WHEN TableX.three = '1'
TableX.one as b1, TableX.two as b2 WHEN TableX.three = '2'
There are other values for TableX.three, but I only want the rows where it's 1 or 2.

The data in TableX looks something like:
id____one_____two_____three
100___10______15______1
100___20______25______2

Basically, I want to get the following data at the end
a1, a2, b1, b2 for each id
All in 1 row. Is this possible?

ex:
Currently, my output looks something like this:
id______a1____a2_____b1_____b2
100____10____ 15____null ____null
100___ null____null____20_____25
where I have 2 rows.

and I want to end up looking like this:
id_____a1____a2_____b1_____b2
100____10____15____20______25
where it makes it all into 1 row.

 

[JMaster]

bump

 

[KLin]

SELECT T.id, T.one as a1, t.two as a2, u.one as b1, u.two as b2
FROM
TableX T INNER Join TableX U on T.ID = U.ID
WHERE
T.Three ='1' AND
U.Three = '2'

 

[UCJefe]

You could always do something like this:

SELECT T1.one AS a1, T1.two AS a2, T2.one AS b1, T2.two AS b2
FROM TableX T1 INNER JOIN TableX T2 ON T1.id = T2.id
WHERE T1.three = 1 OR T1.three = 2

 

[UCJefe]

I'm too slow. And the double whammy is that I overlooked that ID wasn't unique so my WHERE clause won't work. I hand in my SQL card.

 

[Drakkon]

I'm sure theres a better way but:

SELECT a.id, a.one AS a1, b.two AS a2, c.one AS b1, d.two AS b2
FROM test1 a
JOIN test1 b
JOIN test1 c
JOIN test1 d
where
a.three=1 and
b.three=1 and
c.three=2 and
d.three=2


EDIT: I was right...KLIns is much simpler and better he just forgot to select the ID

 

[KLin]

Originally posted by: Drakkon
I'm sure theres a better way but:

SELECT a.id, a.one AS a1, b.two AS a2, c.one AS b1, d.two AS b2
FROM test1 a
JOIN test1 b
JOIN test1 c
JOIN test1 d
where
a.three=1 and
b.three=1 and
c.three=2 and
d.three=2

That seems to be a bit redundant don't you think?

 

[Drakkon]

Originally posted by: KLin

Originally posted by: Drakkon
I'm sure theres a better way but:

SELECT a.id, a.one AS a1, b.two AS a2, c.one AS b1, d.two AS b2
FROM test1 a
JOIN test1 b
JOIN test1 c
JOIN test1 d
where
a.three=1 and
b.three=1 and
c.three=2 and
d.three=2

That seems to be a bit redundant don't you think?

LoL yeah...thats why i said theres gotta be a better way at least I remeberd to select the ID

 

[KLin]

Originally posted by: Drakkon

Originally posted by: KLin

Originally posted by: Drakkon
I'm sure theres a better way but:

SELECT a.id, a.one AS a1, b.two AS a2, c.one AS b1, d.two AS b2
FROM test1 a
JOIN test1 b
JOIN test1 c
JOIN test1 d
where
a.three=1 and
b.three=1 and
c.three=2 and
d.three=2

That seems to be a bit redundant don't you think?

LoL yeah...thats why i said theres gotta be a better way at least I remeberd to select the ID

bah details.

 

[KLin]

You're welcome.