Multiple Pivots

• singh.kamaldeep

  Say Hey Kid

  Points: 703

  More actions

  January 16, 2014 at 6:18 pm

  #281253

  Hi SQL Gurus,

  I am struck into one major issue.

  I have data in my table as follows.

  BOM_STATION_NO STD_DT TIME PERIOD_ID TEMP APPT DEWPT REL_HUM WIND_DIR

  460122000-01-2000:00126.424.38.933.0SSE

  460122000-01-2000:302NULLNULLNULLNULLNULL

  460122000-01-2001:00325.823.89.235.0S

  460122000-01-2001:304NULLNULLNULLNULLNULL

  460122000-01-2002:00524.923.39.237.0S

  460122000-01-2002:306NULLNULLNULLNULLNULL

  460122000-01-2003:00724.322.69.138.0SSW

  460122000-01-2003:308NULLNULLNULLNULLNULL

  460122000-01-2004:00922.921.011.047.0SW

  I need to transform this data so that i have one row for each date for each bom_station_no and have pivot on PERIOD_ID so that the data in the resultant table is as below

  TEMP, APPT , DEWPT, REL_HUM, WIND_DIR, TEMP_0X, APPT_0X , DEWPT_0X, REL_HUM_0X, WIND_DIR_0X where 0X is the period_id till 9.

  It means that i will have 45 columns for the resulting table with just 1 row.

  How can i do that?

  I tried to do with multiple pivots but it says following error "

  The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument."

  Any help will be appreciated.

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004646

  More actions

  January 17, 2014 at 4:35 pm

  #1681304

  singh.kamaldeep (1/16/2014)

  ---

  Hi SQL Gurus,

  I am struck into one major issue.

  I have data in my table as follows.

  BOM_STATION_NO STD_DT TIME PERIOD_ID TEMP APPT DEWPT REL_HUM WIND_DIR

  460122000-01-2000:00126.424.38.933.0SSE

  460122000-01-2000:302NULLNULLNULLNULLNULL

  460122000-01-2001:00325.823.89.235.0S

  460122000-01-2001:304NULLNULLNULLNULLNULL

  460122000-01-2002:00524.923.39.237.0S

  460122000-01-2002:306NULLNULLNULLNULLNULL

  460122000-01-2003:00724.322.69.138.0SSW

  460122000-01-2003:308NULLNULLNULLNULLNULL

  460122000-01-2004:00922.921.011.047.0SW

  I need to transform this data so that i have one row for each date for each bom_station_no and have pivot on PERIOD_ID so that the data in the resultant table is as below

  TEMP, APPT , DEWPT, REL_HUM, WIND_DIR, TEMP_0X, APPT_0X , DEWPT_0X, REL_HUM_0X, WIND_DIR_0X where 0X is the period_id till 9.

  It means that i will have 45 columns for the resulting table with just 1 row.

  How can i do that?

  I tried to do with multiple pivots but it says following error "

  The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument."

  Any help will be appreciated.

  Thanks

  This will require some dynamic SQL. Please see the following article for how to do nearly the same as you've requested.

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

  If you want coded help, I recommend you study the first article in my signature line below under "Helpful Links".

  --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)