FULL JOIN to distribute one column over several others

Hi, I'm constructing a database to replace a previous table kept in excel. The table worked like this:

Number Site1 Site2 Site3
12 x x
14 x x x
16 x x
31 x
82 x x

Where the 'x' represents activity at that particular site. I reconstructed this in my database, and in the large table, I'm storing the Number (along with other details), and in a smaller lookup table, I'm storing:

Details.Number
Details.ActiveSite

So, for the above data, my lookup table would be:

Number Active Site
12 1
12 3
14 1
14 2
14 3
16 1
16 2
31 1
82 2
82 3

Now then, I've constructed this SQL statement, but I'm trying to recreate the previous table, for backwards compatibility... that is, I want ALL the numbers shown on the left, but I'd like to tease out the ActiveSite column and "spread it out" across the top, like it was before.

Here's my statement so far:

SELECT si.Number, sd.ActiveSite
FROM Info AS si
FULL JOIN Details AS sd ON
si.Number = sd.Number
ORDER BY si.Number;

Any help is greatly appreciated. My problem is, if I restrict it by saying "WHERE sd.ActiveSite = 1", then I lose certain numbers...Here is the code for Oracle :

select site_number,
max(decode(activesite, 1, 'x', null)) site1,
max(decode(activesite, 2, 'x', null)) site2,
max(decode(activesite, 3, 'x', null)) site3
from details
group by site_number
/

Hope this helps !!

Originally posted by odinsdream
Hi, I'm constructing a database to replace a previous table kept in excel. The table worked like this:

Number Site1 Site2 Site3
12 x x
14 x x x
16 x x
31 x
82 x x

Where the 'x' represents activity at that particular site. I reconstructed this in my database, and in the large table, I'm storing the Number (along with other details), and in a smaller lookup table, I'm storing:

Details.Number
Details.ActiveSite

So, for the above data, my lookup table would be:

Number Active Site
12 1
12 3
14 1
14 2
14 3
16 1
16 2
31 1
82 2
82 3

Now then, I've constructed this SQL statement, but I'm trying to recreate the previous table, for backwards compatibility... that is, I want ALL the numbers shown on the left, but I'd like to tease out the ActiveSite column and "spread it out" across the top, like it was before.

Here's my statement so far:

SELECT si.Number, sd.ActiveSite
FROM Info AS si
FULL JOIN Details AS sd ON
si.Number = sd.Number
ORDER BY si.Number;

Any help is greatly appreciated. My problem is, if I restrict it by saying "WHERE sd.ActiveSite = 1", then I lose certain numbers...|||Thank you for your help. It's got me thinking about other possibilities. Unfortunately, I'm not using Oracle, though. Here's some things I've tried so far:

SELECT si.Number, COUNT(sd.ActiveSite)
FROM Info AS si
LEFT JOIN Details AS sd ON
si.Number = sd.Number
GROUP BY si.Number;

This ensures that I get Every Single Number from the master table (info), and match it against something in the small (details) table.

This is half-way to where I need to be. What I'd like to do, is narrow the COUNT function further. I'd like to say, "count everything that's equal to 1"

Now, I'm getting back:

Number _____
12 2
14 3
16 2
31 1
82 2

But I'd like to get back:

Number _____ _____ ______
12 1 0 1
14 1 1 1
16 1 1 0
31 1 0 0
82 0 1 1

By doing something like...

SELECT si.Number, COUNT(sd.ActiveSite = 1), COUNT(sd.ActiveSite = 2), COUNT(sd.ActiveSite = 3)
FROM Info AS si
LEFT JOIN Details AS sd ON
si.Number = sd.Number
GROUP BY si.Number;

Again, any help is appreciated. I feel like I'm missing something obvious.|||what database are you using ?|||If you don't have Oracle, maybe you can use dbmadcap's solution but using CASE instead of DECODE:

select site_number,
max(case when activesite = 1 then 'x' else null end) site1,
max(case when activesite = 2 then 'x' else null end) site2,
max(case when activesite = 3 then 'x' else null end) site3
from details
group by site_number|||Originally posted by dbmadcap
what database are you using ?

Microsoft SQL Server.|||Originally posted by andrewst
If you don't have Oracle, maybe you can use dbmadcap's solution but using CASE instead of DECODE:

select site_number,
max(case when activesite = 1 then 'x' else null end) site1,
max(case when activesite = 2 then 'x' else null end) site2,
max(case when activesite = 3 then 'x' else null end) site3
from details
group by site_number

Genius!! This appears to do EXACTLY the right thing! Thanks to you both. dbmadcap especially, for providing the answer first, just, not in a language I knew.

Could you describe the purpose of the max() function in this code? I'm comfortable with the case statement. Does it have to do with needing to use an aggregate function?|||Originally posted by odinsdream
Genius!! This appears to do EXACTLY the right thing! Thanks to you both. dbmadcap especially, for providing the answer first, just, not in a language I knew.

Could you describe the purpose of the max() function in this code? I'm comfortable with the case statement. Does it have to do with needing to use an aggregate function?
Yes, because we don't want to group by these values (otherwise they will get their own rows). MIN would do just as well as MAX. Sometimes, SUM is more appropriate:

SUM(CASE WHEN code='a' THEN 1 ELSE 0 END) as count_of_a