| Author |
Topic |
|
cristianrb
Starting Member
13 Posts |
 Posted - 2006-02-08 : 06:44:10
|
| I have a problem that i dont know how to solve it.I have a table with about 50.000 rows. The structure is something like this: article society---------------------- A1 1 A1 2 A1 3 A1 4 A2 1 A2 2 A2 3 A3 1 A3 2 A3 3 A3 4 A3 5I have to pivot this(new table) to look like this: Article society------------------------ A1 1,2,3,4 A2 1,2,3 A3 1,2,3,4,5I have no damn clue how can i do it. I managed to pivot one row...but its not ok.Thanks for the help! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
cristianrb
Starting Member
13 Posts |
Posted - 2006-02-08 : 09:25:56
|
| Thank you man. It helped :) |
 |
|
|
cristianrb
Starting Member
13 Posts |
Posted - 2006-02-08 : 18:25:29
|
| Hmm...tested it on a 105.000 row table. The amount of time to do this is huge.I made a select distinct top 10 and the number of reads was 800. I dont think this is feasable for such a big table. Any other ideas?...hmmm... |
|
|
|
Job
Yak Posting Veteran
69 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-08 : 18:29:32
|
| The other idea is not to do it in SQL Server. Do it in your application.Tara Kizeraka tduggan |
|
|
|
cristianrb
Starting Member
13 Posts |
Posted - 2006-02-08 : 19:28:03
|
| Well...the application is not home made :)...its bought. The application has something like this but it shows the same article 10 times if the article is found on 10 societies.The application is made in Visual Basic. I have to do it in SQL :). This is an extra report...that was not needed before. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-08 : 19:30:36
|
| The fastest way to do it in T-SQL is via Adam's code (the link that was posted). I see you have posted a comment on his blog, so hopefully he'll be able to help you out.You could try the temp table approach as mentioned in the blog as well. Tara Kizeraka tduggan |
|
|
|
cristianrb
Starting Member
13 Posts |
Posted - 2006-02-09 : 03:21:25
|
| Hmm...well...i cant see any way of optimizing it. First of all i have to mention that the table on which the function applies is not indexed. The Article column is not indexed. The table is the result of a select into from a procedure. Do you think that indexing the table would corect the problem? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-09 : 12:53:13
|
| It certainly would help.Tara Kizeraka tduggan |
|
|
|
cristianrb
Starting Member
13 Posts |
Posted - 2006-02-09 : 15:42:13
|
| Yeap...it shure did...the pivoting lasted only 6 minutes on a 105.000 rows table...with 700.000 reads :) |
|
|
|
|
Pivoting a table