*SPECIAL* query help 1

[akalinowski]

dont know what this is called so i'll try to explain as best i can...

i have a table that looks like this (lest call this table quantities)

STYLE, COLOR,SIZE_RANGE, S1, S2, ....,S18

so S1 - S18 = size "buckets"

the data will look like this:
TEESHIRT1, BLACK, 032, 0, 2, 3, 5, 0,...


the SIZE_RANGE is a code that refereances another table
that looks like this (lets call it SIZES)

SIZE_RANGE, S1, S2, ...S18

and the data in SIZES table would look like this:

032, , S, M, L, , ....

note that some are blank, these are unused size buckets and should return nothing on a report like they do not exist, these are inactive size buckets.

i want to return the following data

TEESHIRT1, BLACK, S, 2
TEESHIRT1, BLACK, M, 3
TEESHIRT1, BLACK, L, 5

what is the best way to do this?

akalinowski

http://www.olivelabs.com

 

[markros]

You need to UNPIVOT data in both tables first, e.g.

;with cteQty as (
--Unpivot the table.
SELECT STYLE, COLOR,SIZE_RANGE, Size_Bucket, Quantity
FROM 
   (SELECT * 
   FROM Quantities) p
UNPIVOT
   (Quantity FOR Size_Bucket IN 
      (S1,S2, S3,..,S18)
) AS unpvt),
cteSizes as (select Size_Range, Size_Bucket, Style
from (select * from Sizes) p 
UNPIVOT (Style FOR Size_Bucket IN (S1,S2,.. S18))
as unpvt) 

select Q.Style, Q.Color, S.Style, Q.Quantity
from cteQty Q inner join cteSizes S on Q.Size_Range = S.Size_Range and Q.Size_Bucket = S.Size_Bucket

From the top of my head - not tested.



PluralSight Learning Library

 

[Emtucifor]

You also need to redesign your database. Truly.