Pivot or Transpose Data

• Suri-154872

  SSC Enthusiast

  Points: 181

  More actions

  February 27, 2006 at 7:42 am

  #112013

  I have this following query:

  SELECT DISTINCT MM.managerName,

    SD.scoreCardName,

    

    (CASE MG.measurementId WHEN 1 THEN MG.goalValue END) AS A,

       (CASE MG.measurementId WHEN 4 THEN MG.goalValue END) AS B,

       (CASE MG.measurementId WHEN 5 THEN MG.goalValue END) AS C,

    MG.scorecardid

   

   FROM dbo.CCSCScorecardManagerModality AS MM(NOLOCK)

    INNER JOIN dbo.CCSCScoreCardMeasurementGoals AS MG(NOLOCK) ON MM.managerId = MG.managerId

    INNER JOIN dbo.CCSCScorecardDetails AS SD(NOLOCK) ON MG.scoreCardId = SD.scoreCardId

    INNER JOIN dbo.CCSCScoreCardMeasurementRelation AS MR(NOLOCK) ON Mg.measurementId = MR.measurementId

   WHERE MG.calendarMonth = 2 and MG.calendarYear = 2006 AND Mg.measurementId IN (1,4,5)

   GROUP BY MG.scorecardid,

    MG.goalValue,

    MG.measurementId,

    MM.managerName,

    SD.scoreCardName

   ORDER BY MM.managerName

  When run, it outputs 2 rows for the same manager and thats is due to the fact the data is not properly transformed from rows to columns(MeasurementID should be columns). The data looks like when run: What I need is the value 80 and 85(goalValue) should be in one row since all other data is repeating

  John Doe Tier 1 Scorecard NULL NULL 80 3

  John Dow Tier 1 Scorecard NULL 85 NULL 3

  Any help will be greatly appreciated.

• daw21

  SSC Veteran

  Points: 274

  More actions

  February 27, 2006 at 10:37 am

  #623498

  You could try

  SELECT DISTINCT MM.managerName,

    SD.scoreCardName,

    

    SUM(CASE MG.measurementId WHEN 1 THEN MG.goalValue END) AS A,

       SUM(CASE MG.measurementId WHEN 4 THEN MG.goalValue END) AS B,

       SUM(CASE MG.measurementId WHEN 5 THEN MG.goalValue END) AS C,

    MG.scorecardid

  .....

  GROUP BY MG.scorecardid,

    MM.managerName,

    SD.scoreCardName