Pivoting tables

Question

Hi all, I have a table like this:

serialnumber   partnb id actual  nominal
1      1  AGR 15,2176803 15,2
1      1  APR 5,8060656 5,8
1      1  DCI 61,9512259 62
1      43  AGR 15,4178727 15,4
1      43  APR 7,235779 7,2
1      43  DCI 52,0080535 52
2      2  AGR 15,2097009 15,2
2      2  APR 5,8009968 5,8
2      2  DCI 61,9582795 62
2      44  AGR 15,4191387 15,4
2      44  APR 7,2370065 7,2
2      44  DCI 52,010244 52

And I want this:

serialnumber   partnb AGR AGR_nominal APR APR_nominal  DCI DCI_nominal
1      1 15,217 15,2  5,806 5,8  61,951 62
1      43 15,417 15,2  7,235 7,2  52,008 62
2      2 15,209 15,2  5,800 5,8  61,958 62
2      44 15,419 15,4  7,237 7,2  52,010 52

How can I achieve this in SQL Server 2005?

Answer 1

There is probably a better way, but you could create separate views for AGR, APR and DCI and join them on id.

Answer 2

There is a PIVOT keyword in Sql Server 2005 you can use. Here is the description http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 3

I have found the solution

  SELECT 
    planid,
    serialnumber,
    partnb,
    DFI,
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DFI') AS nominal_DFI,
    DCI, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DCI') AS nominal_DCI,
    DPI, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DPI') AS nominal_DPI,
    AGR, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='AGR') AS nominal_AGR,
    APR, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='APR') AS nominal_APR,
    DGR, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DGR') AS nominal_DGR,
    DPR, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DPR') AS nominal_DPR,
    DFE, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DFE') AS nominal_DFE,
    DKE, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DKE') AS nominal_DKE,
    MDKE, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='MDKE') AS nominal_MDKE,
    MDKI, 
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='MDKI') AS nominal_MDKI,
    DKI,
    (SELECT nominal FROM vwMeasurement v WHERE p.planid=v.planid 
     AND p.serialnumber=v.serialnumber AND p.partnb=v.partnb 
     AND v.id='DKI') AS nominal_DKI
  FROM(SELECT DISTINCT serialnumber, planid, partnb, id, actual FROM vwMeasurement) driver
  PIVOT (
    SUM(driver.actual) 
    FOR driver.id IN (DFI, DCI, DPI, AGR, APR, DGR, DPR, DFE, DKE, MDKE, MDKI, DKI)
  ) AS p
  ORDER BY planid, serialnumber, partnb