SQL- get data from two tables in different columns without using unions

Question

I have a table STOCK that looks like this:

PRODUCT   SALES_CODE  STOCK_1    STOCK_2    STOCK_3
-----------------------------------------------------
A         6-10        0          1          2

There are many STOCK_X buckets but for simplicity's sake, I've excluded.

Now I have another table SIZE_GRID:

SALES_CODE    SIZE_1   SIZE_2   SIZE_3
--------------------------------------
6-10          6        8        10

As you might have guessed, these are stock on hand for a certain product, by size.

I need to get the STOCK values from the first table, and the size from the second table. Originally, I was doing the following

SELECT
  STOCK.PRODUCT,
  SIZE_GRID.SIZE_1,
  STOCK.STOCK_1
FROM
  STOCK
INNER JOIN
  SIZE_GRID ON
  SIZE_GRID.SALES_CODE = STOCK.SALES_CODE
UNION ALL
SELECT
  STOCK.PRODUCT,
  SIZE_GRID.SIZE_2,
  STOCK.STOCK_2
FROM
  STOCK
INNER JOIN
  SIZE_GRID ON
  SIZE_GRID.SALES_CODE = STOCK.SALES_CODE
UNION ALL
SELECT
  STOCK.PRODUCT,
  SIZE_GRID.SIZE_3,
  STOCK.STOCK_3
FROM
  STOCK
INNER JOIN
  SIZE_GRID ON
  SIZE_GRID.SALES_CODE = STOCK.SALES_CODE

I have around 40 STOCK_X that I need to retrieve, so wandering if there is a much easier way to do this? Preferably I want to use pure SQL and no UDF/SP's.

http://sqlfiddle.com/#!6/f323e

Answer 1

If you are on SQL Server 2008 or later version, you could try the following method (found here):

SELECT
  STOCK.PRODUCT,
  X.SIZE,
  X.STOCK
FROM
  STOCK
INNER JOIN
  SIZE_GRID ON
  SIZE_GRID.SALES_CODE = STOCK.SALES_CODE
CROSS APPLY (
  VALUES
    (SIZE_GRID.SIZE_1, STOCK.STOCK_1),
    (SIZE_GRID.SIZE_2, STOCK.STOCK_2),
    (SIZE_GRID.SIZE_3, STOCK.STOCK_3)
) X (SIZE, STOCK)
;

With a small tweak you could make it work in SQL Server 2005 as well:

SELECT
  STOCK.PRODUCT,
  X.SIZE,
  X.STOCK
FROM
  STOCK
INNER JOIN
  SIZE_GRID ON
  SIZE_GRID.SALES_CODE = STOCK.SALES_CODE
CROSS APPLY (
  SELECT SIZE_GRID.SIZE_1, STOCK.STOCK_1
  UNION ALL
  SELECT SIZE_GRID.SIZE_2, STOCK.STOCK_2
  UNION ALL
  SELECT SIZE_GRID.SIZE_3, STOCK.STOCK_3
) X (SIZE, STOCK)
;

However, if you are using an even earlier version, this might be of help:

SELECT
  STOCK.PRODUCT,
  SIZE  = CASE X.N
            WHEN 1 THEN SIZE_GRID.SIZE_1
            WHEN 2 THEN SIZE_GRID.SIZE_2
            WHEN 3 THEN SIZE_GRID.SIZE_3
          END,
  STOCK = CASE X.N
            WHEN 1 THEN STOCK.STOCK_1
            WHEN 2 THEN STOCK.STOCK_2
            WHEN 3 THEN STOCK.STOCK_3
          END,
FROM
  STOCK
INNER JOIN
  SIZE_GRID ON
  SIZE_GRID.SALES_CODE = STOCK.SALES_CODE
CROSS JOIN (
  SELECT 1
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 3
) X (N)
;

Although the last two options use UNION ALL, they are combining single rows only, not entire subsets

Answer 2

Consider normalizing the table. Instead of a repeating column:

PRODUCT   SALES_CODE  STOCK_1    STOCK_2    STOCK_3

Use a normalized table:

PRODUCT   SALES_CODE   STOCK_NO   STOCK

And the same for the SIZE_GRID table:

SALES_CODE    SIZE_NO   SIZE

Now you can query without the need to list 40 columns:

select  *
from    STOCK s
join    SIZE_GRID sg
on      sg.SALES_CODE = s.SALES_CODE
        and sg.SIZE_NO = s.STOCK_NO