| Author |
 Topic  |
|
|
latent_ken
Starting Member
10 Posts |
 Posted - 08/05/2011 : 14:35:28
|
| Hi I am wondering what is the best way to rectangularize some data. TO explain this to look at one instance. say user_id 1001 has 20 rows of data. How can i move those 20 rows to one row with 20 columns? also I am using T-sql |
Edited by - latent_ken on 08/05/2011 14:36:28
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/05/2011 : 14:51:38
|
If you know the row values that you want to rectangularize (i.e., pivot) in advance, you can use the PIVOT keyword (if you are on SQL 2005 or higher)
If you don't know the row values in advance you will need dynamic pivoting. See Madhivanan's blog. |
 |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 08/05/2011 : 14:58:23
|
If your on 2000 or earlier and you don't need dynamic columns then you can group by user_id and aggregate a case expression for each column. Does each of your 20 rows have some indicator that will determine which column it should appear in? Here is an example of what I mean:
select [user_id]
,max(case when rowtypeid = 1 then val end) as col1
,max(case when rowtypeid = 2 then val end) as col2
,...
,max(case when rowtypeid = 20 then val end) as col20
from YourTable
Group by [User_id]
Be One with the Optimizer
TG |
|
|
|
latent_ken
Starting Member
10 Posts |
Posted - 08/05/2011 : 15:27:15
|
| I dont need it to be dynamic as this is a one time switch. And yes there is indication as to which row should be which column |
|
|
|
latent_ken
Starting Member
10 Posts |
Posted - 08/05/2011 : 15:28:37
|
| the only problem with your solution TG is that there would be 3000+ lines in the select statement for the data that i have. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 08/05/2011 : 15:58:50
|
So you're saying that each user_id can have up to 3000 rows? It would've been nice to mention that in the beginning.
Also, 3000 columns is unwieldy, if not impossible, to use. There's also a limit on the number of columns you can put in a SELECT (4096) or a table (1024, unless you use sparse columns)
|
|
|
|
latent_ken
Starting Member
10 Posts |
Posted - 08/05/2011 : 16:12:54
|
| Yeah a user can have up to 3000 rows, theoretically Because of the way the data matches. Most users have far less but I have to allow the option of it happening. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 08/05/2011 : 17:15:27
|
| If it's based on rowtypeid then you can easily generate the SQL statement. I can't vouch for it compiling though. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
latent_ken
Starting Member
10 Posts |
Posted - 08/16/2011 : 11:42:00
|
| sorry for just getting back i was away. The reason why there are so many is because there are over 3000 questions each with a different question id. in reality each user id will only have 60 questions attached to it but i need to have a table that accommodates all of them. |
|
|
|
latent_ken
Starting Member
10 Posts |
Posted - 08/16/2011 : 11:43:48
|
| the way the data is right now is a user id will have 60 entries with all the same data just a different question id and score |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
| |
Topic |
|
rectangularize data
