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

Besides being tedious, what is the problem with the above approach wrapped into a view? — StrayCatDBA, Jun 17 '13 at 04:56

score 4 · Accepted Answer · edited May 23 '17 at 10:25

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

Worked great. Not only was it much cleaner (from 600 lines to 100 lines), it was also much quicker than unions (from 7 seconds to <1 second). Thanks! — Lock, Jun 17 '13 at 05:38

score 3 · Answer 2 · answered Jun 17 '13 at 04:56

3

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

answered Jun 17 '13 at 04:56

Andomar

232,371
49
380
404

Not an option. It is a very old legacy system. – Lock Jun 17 '13 at 05:01
@Lock Consider normalizing. I think that is the only way to achieve this and preferably a better way. – Soham Dasgupta Jun 17 '13 at 05:02
Not possible as mentioned above. – Lock Jun 17 '13 at 05:37

score 0 · Answer 3 · answered Jun 17 '13 at 04:56

Here are some alternatives you can use:

Execute each SQL separately and merge and sort the result sets within your program
Join the tables.
Use a scalar subquery.

select
select col1, col2, col3 from Table_1 q1,

select col1, col2, col3 from Table_2 q2 from dual;

Try UNION using FULL OUTER JOIN with the NVL function: It is suggested that this has faster performance than the UNION operator.

select
    empno,
    ename,
     nvl(dept.deptno,emp.deptno) deptno, dname from emp
full outer join 
dept 
on
    (emp.deptno = dept.deptno) 
order by 1,2,3,4;

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

3 Answers3