Table Variables: Is There a Cleaner Way?

Question

I have a table that stores various clients I have done work for, separated by Government and Commercial columns. The problem is that there could be an uneven number of clients in either column. When I do a SELECT, I end up with NULL values in irregular places because I can't think of a clean way to order the result set. For example, the following is possible with a straight SELECT (no ORDER BY):

Government | Commercial
DOD        | IBM
DOS        | Microsoft
           | Novell
DVA        | Oracle

As you can see, there is a NULL value in the Government column because there are more commercial clients than government. This could change at any time and there's no guarantee which column will have more values. To eliminate rendering with a blank value in the middle of a result set, I decided to perform two separate SELECTs into table variables (one for the Government clients and another for the Commercial) and then SELECT one final time, joining them back together:

DECLARE @Government TABLE
(
    Row int,
    PortfolioHistoryId uniqueidentifier,
    Government varchar(40),
    GovernmentPortfolioContentId uniqueidentifier
)

DECLARE @Commercial TABLE
(
    Row int,
    PortfolioHistoryId uniqueidentifier,
    Commercial varchar(40),
    CommercialPortfolioContentId uniqueidentifier
)

INSERT INTO @Government
    SELECT
     (ROW_NUMBER() OVER (ORDER BY Government)) AS Row,
     PortfolioHistoryId,
     Government,
     GovernmentPortfolioContentId
    FROM dbo.PortfolioHistory
    WHERE Government IS NOT NULL

INSERT INTO @Commercial
    SELECT
     (ROW_NUMBER() OVER (ORDER BY Commercial)) AS Row,
     PortfolioHistoryId,
     Commercial,
     CommercialPortfolioContentId
    FROM dbo.PortfolioHistory
    WHERE Commercial IS NOT NULL

SELECT
    g.Government,
    c.Commercial,
    g.GovernmentPortfolioContentId,
    c.CommercialPortfolioContentId
FROM @Government AS g
FULL OUTER JOIN @Commercial AS c ON c.Row = g.Row

I'm not necessarily unhappy with this query (maybe I should be), but is there a cleaner way to implement this?

Answer 1

you must have a governID which will connect the goverment table to commercial table. When inserting a values in commercial table you must also insert a governID to where you want the commercial row to be under with.

Government           Commercial
governID     |    NAME       commID     | com_govID   | Name
   1             BIR            1             1          Netopia
                                2             1          SM Mall

so if you query it.

SELECT
    g.Government,
    c.Commercial,
    g.GovernmentPortfolioContentId,
    c.CommercialPortfolioContentId
FROM @Government AS g
FULL INNER JOIN @Commercial AS c ON c.governID = com_govID

Answer 2

generally speaking, rendering issues should be handled in the application layer. Do a straight SELECT, then process it into the format you want.

So:

SELECT DISTINCT client, client_type FROM clients table;

Then in your application layer (I'll use quick and dirty PHP):

foreach($row = $result->fetch_assoc()) {
  if($row['client_type']=='gov') {
     $gov[]=$row['client'];
  } else {
    $com[]=$row['client']; 
  }
}

$limit=(count($gov)>$count($com)) ? count($gov) : count($com);

echo '<table><tr><th>Gov</th><th>Com</th><tr>';
for($i=0; $i< $limit; $i++) {
   echo "<tr><td>{$gov[$i]}</td><td>{$com[$i]}</td></tr>\n";
}
echo '</table>

Answer 3

From design point, I do not see why you need two tables, or even two columns Government/Commercial. You could just have table Clients with a classifier column for OrganizationType. For example:

DECLARE TABLE Customer (
 ID int
 ,Name varchar(50)
 ,OrganizationType char(1)
 ,Phone varchar(12)
 )

For OrganizationType you could use: G=gov, B=business/commercial, C=charity. When querying the table use OrganizationType in ORDER BY and GROUP BY.

If there are some specific columns for gov and business clients, then keep all common columns in the Customer table and move specific columns in separate sub-type Government and Commercial tables as in this example. In the example, book and magazine are types of publication -- in your example government and commercial are types of customer.

Answer 4

SELECT
  Government,
  Commercial,
  GovernmentPortfolioContentId,
  CommercialPortfolioContentId
FROM dbo.PortfolioHistory
ORDER BY
  CASE WHEN Government is null THEN 2 ELSE 1 END,
  CASE WHEN Commercial is null THEN 2 ELSE 1 END,
  Government,
  Commercial

Aside: In your variable tables, the "Row" column should be declared as PRIMARY KEY - to gain the advantages of clustered indexing.