FULL JOIN to distribute one column over several others

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

 

[Parra]

I am sure you can create what you are looking for, using a Crosstab Query. Give it a try, Just use the wizard its pretty simple.

 

[OdinsDream]

Is crosstab in the Query Wizard? I'm constructing this so the results go into Excel, through the Get External Data wizards, so if it's possible to do it with SQL, I can easily implement it.

I'll look into Crosstab. Thanks!

 

[mdmilner]

He's right, PIVOT tables are the right way to do this, and either the wizard or QBE is the 'easy' way to construct it.

TRANSFORM fld1 SELECT tbl1.fld2, tbl1.fld3, tbl1.fld3
FROM tbl1 RIGHT JOIN tbl2 ON tbl1.fld2=tbl2.fld1
GROUP BY tbl1.fld2
PIVOT tbl1.fld3;

 

[OdinsDream]

I found a solution on another forum (dbforums.com) and it goes a little something like this:

SELECT si.Number "Number"
max(case when sd.ActiveSite = 1 then case when sd.Forced IS NULL then 'x' else CAST(sd.Forced AS varchar(3)) end else '' end) [Site1],
max(case when sd.ActiveSite = 2 then case when sd.Forced IS NULL then 'x' else CAST(sd.Forced AS varchar(3)) end else '' end) [Site2],
max(case when sd.ActiveSite = 4 then case when sd.Forced IS NULL then 'x' else CAST(sd.Forced AS varchar(3)) end else '' end) [Site3],
FROM Info AS si
LEFT JOIN Details AS sd ON
si.Number = sd.Number
GROUP BY si.Number

 

[Russell Hauf]

That's some ugly SQL. If you're interested in one crosstab method, here you go:

Make a query off of your table that selects Number, Site, and "x". Also, you could transform the Site into "Site1,Site2,Site3". Something like this:

• Select Number, IIF([Active Site]=1,"Site1",IIF([Active Site]=2,"Site2","Site3") as Site, "x" as Ex FROM MyTable;

Then you can use the crosstab wizard off of this query to make your new table. Choose Number for the Row heading, Site for the Column heading, and Max(Ex) for the value. The SQL would look something like this:

• TRANSFORM Max(qry062503_a2.Ex) AS MaxOfEx
  SELECT qry062503_a2.Number 
  FROM qry062503_a2
  GROUP BY qry062503_a2.Number 
  PIVOT qry062503_a2.Site;

Even though it is 2 queries, it is cleaner in my mind. And if you really wanted one query, you could use a table expression, something like this:

• TRANSFORM Max(Ex) AS SomethingThatWontShow
  SELECT Number 
  FROM (SELECT Number , IIf([Active Site]=1,"Site1",IIf([Active Site]=2,"Site2","Site3")) AS Site, "x" AS Ex
  FROM tbl062503_a)
  GROUP BY Number 
  PIVOT Site;

Where tbl062503_a is my table with 2 fields: Number, and Active Site.

Another way to look at it,

Russell

 

[OdinsDream]

Russell Hauf, I'm trying out your code now.

I should probably point out that the ugly code I have right now has one more important function. If the "x" doesn't exist for a given record, it's replaced with the value in another column.

 

[Russell Hauf]

Russell Hauf, I'm trying out your code now.

I should probably point out that the ugly code I have right now has one more important function. If the "x" doesn't exist for a given record, it's replaced with the value in another column.

I'm not sure what this means.