Unpivoting Data

• Jeff Moden

  SSC Guru

  Points: 1004434

  More actions

  June 15, 2012 at 8:26 am

  #1501708

  ChrisM@Work (6/15/2012)

  ---

  vinu512 (6/15/2012)

  ---

  ChrisM@Work (6/15/2012)

  ---

  anthony.green (6/15/2012)

  ---

  Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.

  I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.

  Thanks again.

  It's much easier to work with than UNPIVOT - the code is far easier to scan and understand.

  Is there a Dynamic Version to it as well??....Would love to have a look at that.

  Vinu, I don't think there's any reason why you shouldn't write a dynamic sql statement which features a CROSS APPLY with a VALUES list.

  I can. There are a whole lot of people that make the mistake of storing temporal data horizontally (think spreadsheet with dates for columns). If you're importing such data, a dynamic solution makes life a whole lot easier.

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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 15, 2012 at 8:34 am

  #1501718

  Jeff Moden (6/15/2012)

  ---

  ChrisM@Work (6/15/2012)

  ---

  vinu512 (6/15/2012)

  ---

  ChrisM@Work (6/15/2012)

  ---

  anthony.green (6/15/2012)

  ---

  Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.

  I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.

  Thanks again.

  It's much easier to work with than UNPIVOT - the code is far easier to scan and understand.

  Is there a Dynamic Version to it as well??....Would love to have a look at that.

  Vinu, I don't think there's any reason why you shouldn't write a dynamic sql statement which features a CROSS APPLY with a VALUES list.

  I can. There are a whole lot of people that make the mistake of storing temporal data horizontally (think spreadsheet with dates for columns). If you're importing such data, a dynamic solution makes life a whole lot easier.

  I'm right with you Jeff, there's a double-negative or something in there somewhere.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden