Help needed with "Pivot" or is it? 1

[ROGERDODGE]

Hi

I'm struggling with trying to put some code together to produce the following:

Table 1

Part_ref AttribType Attrib_Value
ABC101 1 125.00
ABC101 2 433.00
ABC101 3 123.00
XYZ202 1 89.00
XYZ202 2 444.00
XYZ202 3 666.00

etc.

I want to produce

Part_ref Attrib1 Attrib2 Attrib3
ABC101 125.00 433.00 123.00
XYZ202 89.00 444.00 666.00

etc.

I am getting myself in a knot with this, can someone just clear the thinking for me.... I guess my difficulty is that you normally would be summing the values in a Pivot Table??

Rog

 

[r937]

select kkk
     , sum(case when fff = xxx then fff end) as xxx
     , sum(case when fff = yyy then fff end) as yyy
     , sum(case when fff = zzz then fff end) as xxx
  from ttt
group
    by kkk

r937.com | rudy.ca

 

[ROGERDODGE]

Thanks for response and interest, but that is a SUMMATION - what I am after is NOT a SUMMING. Perhaps my example wasnt a good representation.

This is more like it:

Part_ref AttribType Attrib_Value
ABC101 Fitting Left
ABC101 Length 59.60
ABC101 Material Rubber
XYZ202 Fitting Right
XYZ202 Length 62.60
XYZ202 Material Acrylic

etc.

I want to produce:

Part_ref Fitting Length Material
ABC101 Left 59.60 Rubber
XYZ202 Right 62.60 Acrylic

I currently do it with a series of temp tables and Loops with SELECT and UPDATES

I'm hoping there might be an easier (and faster) solution

Kind regards
Roger

 

[r937]

change SUM to MAX and try my query again

SELECT Part_ref
     , MAX(CASE WHEN AttribType = 'Fitting' 
                THEN Attrib_Value END) AS Fitting
     , MAX(CASE WHEN AttribType = 'Length' 
                THEN Attrib_Value END) AS Length
     , MAX(CASE WHEN AttribType = 'Material' 
                THEN Attrib_Value END) AS Material
  FROM Table1
GROUP
    BY Part_ref

;-)

r937.com | rudy.ca

 

[ROGERDODGE]

Awesome!!

It works a treat, and fast too!
Thanks
Rog