Writing dynamic SQL to return columns to unpivot

Question

Writing dynamic SQL to return columns to unpivot

pietlinden

SSC Guru

Points: 63503
More actions
April 2, 2019 at 2:41 am

#2449716

Here's my inherited table definition:
CREATE TABLE Symptoms (
PatientID INT,
Cycle TINYINT,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
ANEMIA TINYINT,
Causality2 TINYINT,
Relatedness2 TINYINT,
ANOREXIA TINYINT,
Causality3 TINYINT,
Relatedness3 TINYINT
CONSTRAINT pkSymptoms PRIMARY KEY (PatientID, Cycle)
);
insert into Symptoms VALUES (1000,1,null,null,null,3,1,2,2,1,1)
,(1000,2,3,1,1,null,null,null,1,2,2)
,(1000,3,1,2,3,1,2,3,3,2,1);
This gets me most of the way:
SELECT PatientID
, Cycle
, ca.Symptom
, ca.Causality
, ca.Relatedness
FROM Symptoms s
CROSS APPLY (VALUES (ALOPECIA, Causality1, Relatedness1),
(ANEMIA, Causality2, Relatedness2),
(ANOREXIA, Causality3, Relatedness3)) ca (Symptom, Causality, Relatedness)
WHERE ca.Symptom IS NOT NULL;
What I really wanted to do was to query the all_columns table and generate the VALUES (<symptom>,causality,relatedness) kind of like this, but I'm not doing it quite right:
DECLARE @ObjID INT;
SELECT @ObjID = object_ID
FROM sys.all_objects ao
WHERE ao.name = 'Symptoms';
SELECT
COL_NAME(@ObjID, ac.column_id) AS Symptom
, COL_NAME(@ObjID, ac.column_id + 1) AS CausalityColumn
, COL_NAME(@ObjID, ac.Column_ID + 2) AS RelatednessColumn
FROM sys.all_columns ac
--WHERE ac.Name in ('ALOPECIA','ANEMIA','ANOREXIA')
WHERE ac.Name = upper(ac.Name)
AND ac.object_id = @objID
AND AC.COLUMN_id>2
AND ac.Name NOT LIKE 'Causality%'
AND ac.Name NOT LIKE 'Relatedness%';
(the ac.Name IN (…) is a cheat. that's the part I wanted to make dynamic.) I feel like I'm really close, but not sure how to fix it.
Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Grrr… Can't I edit my posts anymore?

I think the problem I'm having is that I can't get SQL Server to do a case-sensitive search. Not sure what I'm doing wrong.

SELECT ao.[object_id]

, ac.[name]

, column_id

FROM sys.all_columns AC

INNER JOIN sys.all_objects AO

ON ac.object_id = ao.object_id

WHERE ao.type = 'U'

AND ao.name = 'Symptoms'

AND ao.Name = UPPER(ao.Name) COLLATE SQL_Latin1_General_CP1_CS_AS;

What I'm trying to do is return all columns that are in uppercase, but the above returns no records. In VB you could use STRCOMP(stringA, stringB, vbBinary) and it would do a case sensitive comparison. But I can't figure out how to do it in T-SQL, even after reading the BOL stuff.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

Try using information_schema.columns instead of sys objects

select * from INFORMATION_SCHEMA.COLUMNS

where COLUMN_NAME in ('COLA','COLB','COLC')

***The first step is always the hardest *******