need help in Pivot query

• roshanmaugustine

  SSC Enthusiast

  Points: 144

  More actions

  May 10, 2011 at 3:47 am

  #237120

  Hi All ,

  I have a table with columns

  ProjectID ,YearofOperation WeekNo,PorjectParameterSchedule, ProjectParameterScope,ProjectParameterResources,ProjectParameterQuality

  1 2011 18 2 10 5 18

  1 2011 19 6 8 9 45

  1 2011 20 9 10 7 25

  2 2011 18 2 89 7 12

  2 2011 19 2 9 7 23

  we will diplay one ProjectID at a time Eg; ProjectID =1

  I need the data in the following format

  [18] [19] [20]

  PorjectParameterSchedule 2 6 9

  ProjectParameterScope 10 8 10

  ProjectParameterResources 5 9 7

  ProjectParameterQuality 18 45 25

  Thanks

  Roshan

• WayneS

  SSC Guru

  Points: 95460

  More actions

  May 10, 2011 at 7:05 am

  #1322260

  Please read the articles linked to in my signature:

  For better assistance in answering your questions

  How to ask a question

  Cross Tabs / Pivot Tables, Part 1

  Cross Tabs / Pivot Tables, Part 2

  -- See how you start off by actually creating a table

  -- and then inserting the data into it? Doing this really

  -- makes things a lot easier for all the people you want to

  -- help you. So, HELP US HELP YOU by doing this for us! See

  -- http://www.sqlservercentral.com/articles/Best+Practices/61537/

  -- for more details on how to do all of this.

  IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test;

  CREATE TABLE #test (ProjectID INT,YearofOperation INT, WeekNo INT, PorjectParameterSchedule INT, ProjectParameterScope INT,ProjectParameterResources INT,ProjectParameterQuality int);

  INSERT INTO #test

  VALUES

  (1, 2011, 18, 2, 10, 5, 18),

  (1, 2011, 19, 6, 8, 9, 45),

  (1, 2011, 20, 9, 10, 7, 25),

  (2, 2011, 18, 2, 89, 7, 12),

  (2, 2011, 19, 2, 9, 7, 23);

  -- whew!!! now that the test data has been made,

  -- let's see how to do what you're asking for!

  SELECT ProjectId,

  YearOfOperation,

  Item = 'PorjectParameterSchedule',

  [18] = MAX(CASE WHEN WeekNo = 18 THEN PorjectParameterSchedule ELSE NULL END),

  [19] = MAX(CASE WHEN WeekNo = 19 THEN PorjectParameterSchedule ELSE NULL END),

  [20] = MAX(CASE WHEN WeekNo = 20 THEN PorjectParameterSchedule ELSE NULL END)

  FROM #test

  GROUP BY ProjectID, YearofOperation

  UNION ALL

  SELECT ProjectId,

  YearOfOperation,

  Item = 'ProjectParameterScope',

  [18] = MAX(CASE WHEN WeekNo = 18 THEN ProjectParameterScope ELSE NULL END),

  [19] = MAX(CASE WHEN WeekNo = 19 THEN ProjectParameterScope ELSE NULL END),

  [20] = MAX(CASE WHEN WeekNo = 20 THEN ProjectParameterScope ELSE NULL END)

  FROM #test

  GROUP BY ProjectID, YearofOperation

  UNION ALL

  SELECT ProjectId,

  YearOfOperation,

  Item = 'ProjectParameterResources',

  [18] = MAX(CASE WHEN WeekNo = 18 THEN ProjectParameterResources ELSE NULL END),

  [19] = MAX(CASE WHEN WeekNo = 19 THEN ProjectParameterResources ELSE NULL END),

  [20] = MAX(CASE WHEN WeekNo = 20 THEN ProjectParameterResources ELSE NULL END)

  FROM #test

  GROUP BY ProjectID, YearofOperation

  UNION ALL

  SELECT ProjectId,

  YearOfOperation,

  Item = 'ProjectParameterQuality',

  [18] = MAX(CASE WHEN WeekNo = 18 THEN ProjectParameterQuality ELSE NULL END),

  [19] = MAX(CASE WHEN WeekNo = 19 THEN ProjectParameterQuality ELSE NULL END),

  [20] = MAX(CASE WHEN WeekNo = 20 THEN ProjectParameterQuality ELSE NULL END)

  FROM #test

  GROUP BY ProjectID, YearofOperation

  ORDER BY ProjectID, YearofOperation, item ;

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  May 15, 2011 at 5:26 pm

  #1324815

  roshanmaugustine (5/10/2011)

  ---

  Hi All ,

  I have a table with columns

  ProjectID ,YearofOperation WeekNo,PorjectParameterSchedule, ProjectParameterScope,ProjectParameterResources,ProjectParameterQuality

  1 2011 18 2 10 5 18

  1 2011 19 6 8 9 45

  1 2011 20 9 10 7 25

  2 2011 18 2 89 7 12

  2 2011 19 2 9 7 23

  we will diplay one ProjectID at a time Eg; ProjectID =1

  I need the data in the following format

  [18] [19] [20]

  PorjectParameterSchedule 2 6 9

  ProjectParameterScope 10 8 10

  ProjectParameterResources 5 9 7

  ProjectParameterQuality 18 45 25

  Thanks

  Roshan

  You need a "dynamic Cross -Tab" for this. Please see the following article to familiarize yourself with the process. If you still have problems with making this "non-hard-coded", post back.

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)