Query to create 1 row from 13 1

[JSHoltsIT]

I have a query that pulls product attributes from a temp table and marries the values with data in a table. My intent is to take each of the 13 rows and create a single row with the values from @productAttributes (attributeLabel in the query results) becoming the column name and the values from ext40102.LONGNAME populating the corresponding field.

The first column in the row should be ext00103.PT_UD_Key followed by the 13 columns listed above. This will need to run for 2500+ products that all have 13 attributes to list.

Here is a screen shot of the current query output

Thanks in advance.

Jason

declare @productAttributes table (PT_UD_Number tinyint, attributeLabel VarChar(50))

INSERT INTO @productAttributes (PT_UD_Number, attributeLabel)
SELECT 1,'Type'
UNION ALL
SELECT 2, 'Brand'
UNION ALL
SELECT 3, 'Product Line'
UNION ALL
SELECT 4, 'Product'
UNION ALL
SELECT 5, 'Quantity'
UNION ALL
SELECT 6, 'Length'
UNION ALL
SELECT 7, 'Ring'
UNION ALL
SELECT 8, 'Country of Origin'
UNION ALL
SELECT 9, 'Wrapper'
UNION ALL
SELECT 10, 'Binder'
UNION ALL
SELECT 11, 'Filler'
UNION ALL
SELECT 12, 'Strength'
UNION ALL
SELECT 14, 'Class'

select Top 13
--ext00103.PT_Window_ID
ext00103.PT_UD_Key
, ext40102.LONGNAME
--, ext00103.PT_UD_Number
, (SELECT attributeLabel from @productAttributes WHERE PT_UD_Number = ext00103.PT_UD_Number) as attributeLabel
from mail..ext00103 ext00103, mail..ext40102 ext40102
where
--ext00103.PT_UD_KEY = 'CAS898'
ext00103.PT_Window_ID = 'ECOMM_ATTRIB'
AND ext40102.PT_Window_ID = 'ECOMM_ATTRIB'
AND ext00103.PT_UD_Number = ext40102.Field_Number
AND ext00103.TOTAL = ext40102.LNITMSEQ
ORDER BY ext00103.PT_UD_KEY,ext00103.PT_UD_Number

 

[pjw001]

I think the simplest way would be to insert into the table the distinct values of PT_UD_KEY.

Having done that write 13 update statements, one for each of the remaining columns of the table.

 

[JSHoltsIT]

Is there a way to use a for next loop on each recordset to accomplish this?

 

[gmmastros]

Try this...

Select	ext00103.PT_UD_Key,
		Min(Case When PT_UD_Number = 1 Then LongName End) As [Type],
		Min(Case When PT_UD_Number = 2 Then LongName End) As [Brand],
		Min(Case When PT_UD_Number = 3 Then LongName End) As [Product Line],
		Min(Case When PT_UD_Number = 4 Then LongName End) As [Product],
		Min(Case When PT_UD_Number = 5 Then LongName End) As [Quantity],
		Min(Case When PT_UD_Number = 6 Then LongName End) As [Length],
		Min(Case When PT_UD_Number = 7 Then LongName End) As [Ring],
		Min(Case When PT_UD_Number = 8 Then LongName End) As [Country of Origin],
		Min(Case When PT_UD_Number = 9 Then LongName End) As [Wrapper],
		Min(Case When PT_UD_Number = 10 Then LongName End) As [Binder],
		Min(Case When PT_UD_Number = 11 Then LongName End) As [Filler],
		Min(Case When PT_UD_Number = 12 Then LongName End) As [Strength],
		Min(Case When PT_UD_Number = 12 Then LongName End) As [Class]
from    mail..ext00103 ext00103
        Inner Join mail..ext40102 ext40102
          On  ext00103.PT_UD_Number = ext40102.Field_Number
          AND ext00103.TOTAL = ext40102.LNITMSEQ
where   ext00103.PT_Window_ID = 'ECOMM_ATTRIB'
        AND ext40102.PT_Window_ID = 'ECOMM_ATTRIB'
Group By ext00103.PT_UD_Key
ORDER BY ext00103.PT_UD_KEY

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JSHoltsIT]

George - You ROCK!

Thanks

Jason

 

[gmmastros]

Did you catch the mistake?

Min(Case When PT_UD_Number = [!]12[/!] Then LongName End) As [[!]Strength[/!]],
Min(Case When PT_UD_Number = [!]12[/!] Then LongName End) As [[!]Class[/!]]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JSHoltsIT]

yes, it should be 14.

You still rock.

Jason