Combining multiple rows to create a new row with the same ID

Question

I'm trying to modify a table format to a new format which will help me in a new .Net application I'm working on. The change is simple but I can't work it out.

Here is a sample of the current format:

 RecoNumber   Position  Type            Length     

 NO VON-01    Top       Spiralbelt      44.27274    
 NO VON-01    Bottom    Venta Trac      13.10656    
 ACMHAPA-01   Top       Rhino Hyde-B    28.12       
 ACMHAPA-01   Lagging   OPL-35          0           
 ADVGRMI-01   Top       Spirabelt 1.05  35.40905    
 ADVGRMI-01   Bottom    Venta Trac-B    13.05474    
 ADVGRMI-01   Lagging   BMD-6           0           

And here is a sample of how the format should be in the end:

RecoNumber   TopType         BtmType       LaggingType   TopLength   BtmLength   LaggingLength   

NO VON-01    Spiralbelt      Venta Trac    NULL          44.27274    13.10656    NULL
ACMHAPA-01   Rhino Hyde-B    NULL          OPL-35        28.12       NULL        0
ADVGRMI-01   Spirabelt 1.05  Venta Trac-B  BMD-6         35.40905    13.05474    0

I have tried using a Group By or the Row_Number() Function to merge the but I couldn't get it to work. The closest attempt I have gotten so far is by isolating each cell but I am unable to merge them all into a single row.

Here is where I am so far:

SELECT  RecoNumber,
        CASE WHEN (Position = 'Top') THEN Type ELSE NULL END as TopType,
        CASE WHEN (Position = 'Top') THEN Length ELSE NULL END as TopLength,
        CASE WHEN (Position = 'Bottom') THEN Type ELSE NULL END as BtmType,
        CASE WHEN (Position = 'Bottom') THEN Length ELSE NULL END as BtmLength,
        CASE WHEN (Position = 'Lagging') THEN Type ELSE NULL END AS LaggingType,
        CASE WHEN (Position = 'Lagging') THEN Length ELSE NULL END as LaggingLength
FROM Workbook2014_Test.dbo.RecoBeltsOld

result:

RecoNumber  TopType        TopLength  BtmType      BtmLength    LaggingType    LaggingLength

NO VON-01   Spiralbelt     44.27274   NULL         NULL         NULL           NULL
NO VON-01   NULL           NULL       Venta Trac   13.10656     NULL           NULL
ACMHAPA-01  Rhino Hyde-B   28.12      NULL         NULL         NULL           NULL
ACMHAPA-01  NULL           NULL       NULL         NULL OPL-35  0
ADVGRMI-01  Spirabelt 1.05 35.40905   NULL         NULL         NULL           NULL
ADVGRMI-01  NULL           NULL       Venta Trac-B 13.05474     NULL           NULL
ADVGRMI-01  NULL           NULL       NULL         NULL         BMD-6          0

Thanks for any tips, Simon

Answer 1

You could use a group by to get them all on the same RecoNumber row.

SELECT  RecoNumber,
        max(CASE WHEN (Position = 'Top') THEN Type ELSE NULL END) as TopType,
        max(CASE WHEN (Position = 'Top') THEN Length ELSE NULL END) as TopLength,
        max(CASE WHEN (Position = 'Bottom') THEN Type ELSE NULL END) as BtmType,
        max(CASE WHEN (Position = 'Bottom') THEN Length ELSE NULL END) as BtmLength,
        max(CASE WHEN (Position = 'Lagging') THEN Type ELSE NULL END) AS LaggingType,
        max(CASE WHEN (Position = 'Lagging') THEN Length ELSE NULL END) as LaggingLength
FROM Workbook2014_Test.dbo.RecoBeltsOld
group by RecoNumber