August 14, 2008 at 12:20 pm
So i am dealing with a survey data database. Incoming data are always in a horizontal format, meaning each row is a respondent to the survey, each column is a separate question, and each data is the response.
For storing the data in a DB, especially when different surveys have number of questions (hence different column headers), we also pivot / normalize it in a vertical format. For example, the columns are SurveyID, RespondentID, QuestionID, response.
However, at any time, the user should have the ability to choose the whole set, or a subset of the questions, and export it back out into horizontal format (the way they report it)
My now biggest problem is that, what if the survey has more than 1024 columns, or the row has more than 8060 bytes (which happens quite a lot). is there any way to trick SS2005 to have more than 1024 columns? or more than 8060 bytes?
If not, then that means a normal pivot won't work. Then what would be the easiest way to go back and forth between the formats?
We developed some loading mechanism in SSIS that splits the file into multiple tables, then pivot them individually and load it in. It is a little trickier when outputing, though not impossible, but definitely not ideal.
Also, if we can get around with the size limit, then we can provide dynamic temp table for the advanced user to query the db in a horizontal format.
this link gives me some hope
http://msdn.microsoft.com/en-us/library/ms143432.aspx
as it says
Columns per nonwide table
1,024
1,024
Columns per wide table
30,000
30,000
never heard of wide table before, and can't find much so far
sorry for the long post, and thanks in advance!!
August 14, 2008 at 1:59 pm
I don't think SQL 2008 wide tables will do what you need. They're more about having optional columns in a table, if I'm not mistaken.
The potential solution that occurs to me for your situation would be to handle the data with XML when you want to output all those columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2008 at 1:35 am
anyone has any idea for sql server 2005 then?
please help.. thanks
August 18, 2008 at 8:15 am
You'll either need to output it as two data sets or as XML. Since I suggested XML above, and you're still asking, I'll assume that's not a viable solution for you.
You could take the first 1024 columns and output that as one file, then the rest of the columns (or the second 1024, if it's more than 2048), and output that as a second column. So long as you have the primary key in both outputs, that should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply