| Author |
Topic |
|
latent_ken
Starting Member
10 Posts |
 Posted - 2011-08-05 : 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 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-05 : 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-05 : 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 col20from YourTableGroup by [User_id] Be One with the OptimizerTG |
|
|
|
latent_ken
Starting Member
10 Posts |
Posted - 2011-08-05 : 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 - 2011-08-05 : 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
15732 Posts |
Posted - 2011-08-05 : 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 - 2011-08-05 : 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
15732 Posts |
Posted - 2011-08-05 : 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
38200 Posts |
|
|
latent_ken
Starting Member
10 Posts |
Posted - 2011-08-16 : 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 - 2011-08-16 : 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
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-08-17 : 09:05:24
|
| Follow the link "How to ask" below. It will help you give us the data we need to help you.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
rectangularize data