Return Value from table based on column title?

Transact-SQL
1

I'm not 100% sure how to word this....

I have 2 tables
PO with column names:
Num, Name, Sizescale, R1, R2, R3

  • Sizescale references another table
  • R1 is a number 1-x (quantity)

SIZES with column names
Sizescale, S1, S2, S3

  • S1 has the size value (ex: 7)
  • S2 may have the size 8

How can i retrieve the results that look like this:

  • po.num, po.name, sizes.S1 as size, po.R1 as qty

And have a new row for each size? I can do an export of the data in a bit if that would help.

Thanks in advance.
Brian

2

I think you could use something like:

SELECT po.num, po.name, sizes.S1 as size, po.R1 as qty
FROM PO inner join Sizes on po.sizescale = sizes.sizescale
UNION
SELECT po.num, po.name, sizes.S2 as size, po.R1 as qty
FROM PO inner join Sizes on po.sizescale = sizes.sizescale
UNION
SELECT po.num, po.name, sizes.S3 as size, po.R1 as qty
FROM PO inner join Sizes on po.sizescale = sizes.sizescale

You may need to check for null.
This is just one way there are others that may be better.

3

thanks for the quick reply. you think I would have to use a union for all the different scale sizes? i believe there to be 16 or so.

4

Sorry, I have written this only referring to the S1, S2 size columns and not also the R1, R2 columns, also I have referred to the S1, S2 columns being in the PO table, rather than the SIZES table. Rather than trying to fix it up hopefully you will be able to understand what I mean, if not please ask and I'll go back and fix it properly, but right now I'm supposed to be heading out :smile:

It would be better that all your S1, S2 size columns were actually individual rows in a separate child table. Probably too late to change the database design now?

If it is tedious to write the UNION query, but you need to use it often, you could create a VIEW that includes all the necessary UNIONs and then just query that VIEW when you want the sizes "flattened"

You could create a Child Table, for the sizes, and have a Trigger on the parent table that automatically maintained the Child Table in step. All existing APPs would work, with the original table, but queries could use the Child Table instead, if they prefer.

You could rename the existing table to a "Version2" name and make that the main table, and create a Child Table for the Sizes, and "move" the size data from PO_V2 to the Sizes child table. Then create a view with the original PO table name that flattens the parent / child data back into the original format - again, all existing SELECT APPs will continue to work; but UPDATE/INSERT/DELETE will not work, for them create an INSTEAD OF trigger on the PO View and have that write to the new PO_V2 table and the Sizes child table as appropriate.

All that is quite a lot of work but, if you now decide that a re-design is needed (with the benefit of hindsight) it is possible to implement it with minimal, if any, changes to existing APP code.

P.S. I wonder if you should use "UNION ALL" rather than "UNION". If someone puts "Size 8" in BOTH the S1 AND S2 columns (of the same row) then that is presumably two entries, not one, even if that should not be allowed. (Something else that is MUCH easier to enforce on a Child Table - just put a Unique Constraint / Index on Num, SIZE columns in the new V2 table)

5

Forget UNIONs across the table (unless you're still on SQL 2000, yikes!). You can use CROSS (or OUTER) APPLY to do this more easily and efficiently. If you're not yet on SQL2008+, you'll have to change the VALUES clause below to do UNIONs within the APPLY

SELECT PO.Num, PO.Name, PO.Sizescale, 
    CASE size_counter
        WHEN 1 THEN PO.R1
        WHEN 2 THEN PO.R2
        WHEN 3 THEN PO.R3
    END AS Qty,
    ca.size
FROM PO
INNER JOIN SIZES ON SIZES.Sizescale = PO.Sizescale
CROSS APPLY (
    VALUES(1, S1), (2, S2), (3, S3)
) AS ca(size_counter, size)
1 Like