FULL JOIN to distribute one column over several others

odinsdream · #1 (**permalink**) 06-17-03, 10:50

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

Code:

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:

Code:

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:

Code:

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...

dbmadcap · #2 (**permalink**) 06-17-03, 11:35

Here is the code for Oracle :

Code:

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 !!

Quote:

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

Code:

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:

Code:

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:

Code:

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...

odinsdream · #3 (**permalink**) 06-18-03, 11:16

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:

Code:

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:

Code:

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

But I'd like to get back:

Code:

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...

Code:

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.

dbmadcap · #4 (**permalink**) 06-18-03, 11:48

what database are you using ???

andrewst · #5 (**permalink**) 06-18-03, 12:37

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

odinsdream · #6 (**permalink**) 06-18-03, 13:11

Quote:

Originally posted by dbmadcap
what database are you using ???

Microsoft SQL Server.

odinsdream · #7 (**permalink**) 06-18-03, 13:17

Quote:

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?

andrewst · #8 (**permalink**) 06-18-03, 13:23

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech