December 2, 2013 at 10:40 pm
I was reading Kenneth Fisher's article on using Cross Apply to normalize an unnormalized table. (I had to do this a long time ago in Access, and it was a nightmare, so the article caught my attention.)
In his case, he is unpivoting (Question, Answer) pairs, and the number of "pairs" is static. Here's the twist... in my case, I had TONS of databases that were like this, and the number of (Q,A) pairs was not static. On top of that, the columns were something like you'd find in a spreadsheet, so instead of Question[n], I would have a Symptom name.
So my table was something like this:
CREATE TABLE ToxicityPivot(
PatientID int NOT NULL,
Cycle tinyint NOT NULL,
Toxicity1 tinyint,
Causality1 tinyint,
Relatedness1 tinyint,
...
);
ALTER TABLE ToxicityPivot ADD CONSTRAINT pkUnpivot PRIMARY KEY (PatientID, Cycle);
... except that the three "subscripted" (Toxicity, Causality, Relatedness0 columns could repeat an indeterminate number of times. (Some of my tables had 70+ columns!) On top of that, instead of Toxicity[n] as a column name, I had a very large number of possible values. (I think there are several thousand symptoms listed in the Common Toxicity for Adverse Events list).
How would I grab the names of the "toxicity[n]" columns? In Access, I did it using the Fields collection (dirty word around here, I know!). Basically walk the Fields collection of the table object and then grab the column names that were in upper case... (they were at least wrong in a consistent way!)
here's an example of what the table structure was like:
CREATE TABLE Toxicity(
PatientID int NOT NULL,
Cycle int NOT NULL,
ALOPECIA tinyint,
Causality1 tinyint,
Relatedness1
BLOOD tinyint,
Blood_Specify VARCHAR(30),
Causality2 tinyint,
Relatedness2 tinyint
etc...
);
(Some symptoms had a "specify" column - there's no way to know from just the name - I would "learn" which columns did by seeing lots of examples.)
Can I do the same thing in T-SQL? If so, how? (Don't need a detailed description, but what system tables would I look at?)
Thanks!
Pieter
December 3, 2013 at 5:59 pm
Take a look at these two articles by Jeff Moden on Crosstabs:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns [/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]
The second one will show you the way of the Jedi.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply