0

I have a data base that records the attributes of users at certain periods of time. A simplified example:

UserID  Year Measure1 Measures2
---------------------------------
1,       2000,     3,     4
1,       2001,     5,     6

What I need to do is collapse multiple rows of data into columns

UserID Measure1_2000  Measure1_2001
-----------------------------------
1,      3,               5

Where the end result is that each row tracks users attributes across time. I would prefer to write this as a function or stored procedure, where the number of years that are collapsed into columns is supplied as an argument, so the table structure has to be generated dynamically.

I could use a big table pivot statement, but then the number of columns would be static, and the code would be messy, because I have to collapse multiple types of data into columns.

I tried using some dynamic sql that would iterate through a while loop, joining a list of user ids against the master table, but it is way, way too slow. Does anybody have any ideas?

James Z
  • 12,209
  • 10
  • 24
  • 44
user3538411
  • 338
  • 4
  • 15
  • 3
    This has been asked and answered hundreds and hundreds of times around here. Search for dynamic pivot. You won't be able to do this in a function because it will require dynamic sql. http://stackoverflow.com/search?q=[sql-server]dynamic+pivot – Sean Lange Feb 18 '16 at 17:25
  • Hi Sean, thank you. I googled and searched quite a bit. I haven't been able to find what I need yet, or I don't understand what I am looking at :). Would you be able to point me to a specific example? Thank you! – user3538411 Feb 18 '16 at 17:49
  • 1
    Sure. Click the link I posted and view the very first one on the list. – Sean Lange Feb 18 '16 at 17:52
  • Definitely didn't understand what I was looking at! I think I am piecing it together. That is enough to get me started, thanks. – user3538411 Feb 18 '16 at 18:15
  • 1
    Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Tab Alleman Feb 18 '16 at 19:37
  • @user3538411 I found this example that looks very similar to what you're trying to do.. http://sqlfiddle.com/#!3/8b473b/1 good luck :) – JamieD77 Feb 18 '16 at 20:07

0 Answers0