pietlinden (2/28/2015)
I've been trying to figure out how to answer this for a couple of days at least...I faced a situation like this once, but it was in Access (yup, SQL Server's red haired stepchild). You could maybe use Access to grab the column names (which contain information), and somewhat untwist the data, and then once you clean that up (maybe writing to a local Access table), write the whole thing to SQL Server.
Then handy thing about Access is that you can use VBA, which lets you deal with the columns... maybe it's just that I find VBA easier to deal with than dynamic SQL and the sys.Columns table...
If you're interested, let me know and I'll see if I can come up with a way to "preprocess" this stuff in Access.
Is there any way you could fix the data before getting it into SQL Server? So that your table might look something like this:
TestID,
Reading, -- the value
PercentileRank,
PGS,
RankNumber
The problem I had was that people were storing information in column names, and that makes a huge mess. Especially if you have to filter and group values.
Thanks pietlinden, I appreciate your reply. I don't know much VBA (just a little for SSRS). Would this automate the process?
It sounds like it might but I have a worker who doesn't know VBA so he spends endless days processing data everyday to massage the data with Access. It takes him a week to do one monthly report and it's a disaster when the boss (he's non technical) said to him last week: You have to regenerate the reports because we have some new data that just came in. I feel so sorry for the guy. But I digress. You know VBA and he does not. It would probably cheer him up a lot to see an automated VBA example even if his tasks are different from mine.
If you have the time and interest, I would very much appreciate an example.