February 12, 2013 at 3:27 pm
I have a table as follows:
Row#, Account#, ..., Column_40
With data that looks like:
1, 100, ...., N
2, 100, ...., N
3, 101, ...., N
4, 102, ...., N
5, 103, ...., N
6, 103, ...., N
The row number is unique, but the Account# can be repeated on rows. What I need to do is put all information for a given account number on the same row.
So the number of resulting columns would be 40 x 'the number of times an account# appears in the table' (ex. if the most times an account# appears is 6, there would be 240 columns.
What is the best way to accomplish this? With Recursion or Dynamic Pivot or ?
Thank you for any guidance you can provide. I could just do a count on the account# then create the columns in advance and then do the join, but that wouldn't be very efficient for future work that is similar.
Thank you again!
February 12, 2013 at 6:13 pm
I believe this article by Jeff Moden is exactly what you need:
http://qa.sqlservercentral.com/articles/Crosstab/65048/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply