SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 rectangularize data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

latent_ken
Starting Member

10 Posts

Posted - 08/05/2011 :  14:35:28  Show Profile  Reply with Quote
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

5155 Posts

Posted - 08/05/2011 :  14:51:38  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/05/2011 :  14:58:23  Show Profile  Reply with Quote
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
Go to Top of Page

latent_ken
Starting Member

10 Posts

Posted - 08/05/2011 :  15:27:15  Show Profile  Reply with Quote
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
Go to Top of Page

latent_ken
Starting Member

10 Posts

Posted - 08/05/2011 :  15:28:37  Show Profile  Reply with Quote
the only problem with your solution TG is that there would be 3000+ lines in the select statement for the data that i have.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/05/2011 :  15:58:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)

Go to Top of Page

latent_ken
Starting Member

10 Posts

Posted - 08/05/2011 :  16:12:54  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/05/2011 :  17:15:27  Show Profile  Visit robvolk's Homepage  Reply with Quote
If it's based on rowtypeid then you can easily generate the SQL statement. I can't vouch for it compiling though.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 08/05/2011 :  17:45:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do not "rectangularize" your data if you are going to have so many columns. This is a terrible design. Can you explain why you want to this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

latent_ken
Starting Member

10 Posts

Posted - 08/16/2011 :  11:42:00  Show Profile  Reply with Quote
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.
Go to Top of Page

latent_ken
Starting Member

10 Posts

Posted - 08/16/2011 :  11:43:48  Show Profile  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 08/17/2011 :  09:05:24  Show Profile  Reply with Quote
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.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000