Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    10

    Unanswered: 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:

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

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: FULL JOIN to distribute one column over several others

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

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

  3. #3
    Join Date
    Jun 2003
    Posts
    10
    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.

  4. #4
    Join Date
    May 2003
    Posts
    87
    what database are you using ???

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  6. #6
    Join Date
    Jun 2003
    Posts
    10
    Originally posted by dbmadcap
    what database are you using ???
    Microsoft SQL Server.

  7. #7
    Join Date
    Jun 2003
    Posts
    10
    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?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •