Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

*SPECIAL* query help 1

Status
Not open for further replies.

akalinowski

IS-IT--Management
Oct 3, 2006
190
US
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
 
You need to UNPIVOT data in both tables first, e.g.
Code:
;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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top