January 15, 2014 at 9:43 pm
I was reading Kenneth Fisher's[/url] and Dwain Camps[/url]' articles on unpivoting using cross apply...
and I can actually get them to work....
CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
ANEMIA TINYINT,
Causality2 TINYINT,
Relatedness2 TINYINT,
BLOOD TINYINT,
Causality3 TINYINT,
Relatedness3 TINYINT);
GO
INSERT INTO #TxCycle VALUES (1,1000,3,1,1,NULL,NULL,NULL,2,2,2);
INSERT INTO #TxCycle VALUES (1,1001,2,2,1,3,1,1,NULL,NULL,NULL);
SELECT ca.PatientID
, ca.Cycle
, ca.Grade
, ca.Causality
, ca.Relatedness
FROM
(
SELECT PatientID
, Cycle
, CrossApplied.Grade, CrossApplied.Causality, CrossApplied.Relatedness
FROM #TxCycle
CROSS APPLY (VALUES (Alopecia, Causality1, Relatedness1),
(Anemia, Causality2, Relatedness2),
(Blood, Causality3, Relatedness3))
CrossApplied (Grade,Causality,Relatedness)
) ca
WHERE ca.Grade IS NOT NULL;
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
Thanks!
Pieter
January 15, 2014 at 10:59 pm
pietlinden (1/15/2014)
CREATE TABLE #TxCycle(Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
ANEMIA TINYINT,
Causality2 TINYINT,
Relatedness2 TINYINT,
BLOOD TINYINT,
Causality3 TINYINT,
Relatedness3 TINYINT);
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand?
Peter,
Let me try to make sure I understand what you're asking here. You have a table like the one above which contains a bunch of symptom names, which I have bolded. However you don't know in advance what the names all are?
Why not, if you already know the first 3? Because some might be added in the future?
It would be possible to extract the column names from the catalog and then use that to construct some dynamic sql, using the column list as the first entry in your VALUEs (table row constructor). But you would need a way to distinguish symptoms from other columns in the table, e.g., TINYINT + some variations as in NOT LIKE 'Causality%'
Am I getting close?
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
January 15, 2014 at 11:17 pm
Dwain, I think you get it. The problem was that everything was in Access, and so I ended up writing some really crazy VBA to union the stuff together so I could query it. The VBA wrote a SQL statement that grabbed the column names as necessary (for the symptoms) and then UNIONed the values together. Big ugly mess. I suppose it wouldn't be so much of an issue in SQL Server because no self-respecting DBA would let someone who thought that was an acceptable design even near his database, never mind having developer-type rights.
In a nutshell, the hard part is dealing with the column names and evaluating them. I guess that's next on my to do list.
Gotta say, I'm learning a lot! (even if it is some "monkey see, monkey do", but you gotta start somewhere!
January 15, 2014 at 11:42 pm
Maybe this will help then.
CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
ANEMIA TINYINT,
Causality2 TINYINT,
Relatedness2 TINYINT,
BLOOD TINYINT,
Causality3 TINYINT,
Relatedness3 TINYINT);
SELECT OBJECT_ID('tempdb..#TxCycle')
SELECT name
FROM tempdb.sys.all_columns
WHERE object_id = OBJECT_ID('tempdb..#TxCycle') AND
system_type_id = 48 AND
name NOT LIKE 'Causality%' AND name NOT LIKE 'Relatedness%';
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL =
N'
SELECT ca.PatientID
, ca.Cycle
, ca.Grade
, ca.Causality
, ca.Relatedness
FROM
(
SELECT PatientID
, Cycle
, CrossApplied.Grade, CrossApplied.Causality, CrossApplied.Relatedness
FROM #TxCycle
CROSS APPLY
(
VALUES ' +
STUFF(
(
SELECT ',(' + name + ', Casuality' + CAST(n AS VARCHAR) + ', Relatedness' + CAST(n AS VARCHAR) + ')' + CHAR(10)
FROM
(
SELECT name, n=ROW_NUMBER() OVER (ORDER BY column_id)
FROM tempdb.sys.all_columns
WHERE object_id = OBJECT_ID('tempdb..#TxCycle') AND
system_type_id = 48 AND
name NOT LIKE 'Causality%' AND name NOT LIKE 'Relatedness%'
) a
ORDER BY n
FOR XML PATH('')
), 1, 1, '') +
N' ) CrossApplied (Grade,Causality,Relatedness)
) ca
WHERE ca.Grade IS NOT NULL;';
PRINT @SQL
--EXEC sp_executesql @SQL
GO
DROP TABLE #TxCycle;
Assuming you're not looking for a temp table, you'll need to look for the proper OBJECT_ID in sys.all_columns (instead of tempdb.sys.all_columns).
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
January 21, 2014 at 12:12 pm
Dwain,
cool code! I'll have to dig in and figure out how it works. One thing is missing though... the Symptom name disappeared. The final output should look like this:
CREATE TABLE #Results(
PatientID INT NOT NULL,
Symptom VARCHAR(25) NOT NULL,
Grade TINYINT NOT NULL,
Cycle TINYINT NOT NULL,
Causality TINYINT,
Relatedness TINYINT);
How do I retrieve the column name the "easy" way? (I know, it's a bit of a stretch!)
I did it in Access using VBA, and I could loop the table using the Fields collection... and use tabledef.Fields(n).Name to retrieve the column name containing the Symptom.
January 21, 2014 at 12:29 pm
Dwain,
cool code! I'll have to dig in and figure out how it works. One thing is missing though... the Symptom name disappeared. The final output should look like this:
CREATE TABLE #Results(
PatientID INT NOT NULL,
Symptom VARCHAR(25) NOT NULL,
Grade TINYINT NOT NULL,
Cycle TINYINT NOT NULL,
Causality TINYINT,
Relatedness TINYINT);
How do I retrieve the column name the "easy" way? (I know, it's a bit of a stretch!)
I did it in Access using VBA, and I could loop the table using the Fields collection... and use tabledef.Fields(n).Name to retrieve the column name containing the Symptom.
January 21, 2014 at 5:32 pm
pietlinden (1/21/2014)
Dwain,One thing is missing though... the Symptom name disappeared.
Symptom name is just the column name right?
Focus on the code I've embedded in the (SQL) string creation to extract the column names (the column with the name has the unlikely name of "name" :-D) from sys.all_columns. Add that as a column to the VALUES (table row constructor) enclosed in quotes (followed by a comma) and add a new column "Symptom" as the results from the CrossApplied (aliased) derived table.
Sounds difficult when I say it that way but in reality it is not.
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
January 21, 2014 at 5:43 pm
Okay. I could get the name of the column outside of the code, but not inside. I'll give it another try and see if I can get closer. (Only one way to learn... try, try again!)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply