SQL Server - column names as variables?

Factual Questions
1

CREATE TABLE MyTable(
Banana int,
Cherry int,
Lawnmower char(1),
Pear int,
Rake char(1),
SkankyHoe char(1)
)

DECLARE @STRING varchar(500)

WHILE (some condition)
BEGIN
@STRING = ‘SELECT * FROM MyTable WHERE’ + (column name of some kind of lawn implement) + ’ = ‘‘Y’’’
EXEC (@STRING)
(advance the while condition)
END

It actually gets more complicated than that, but what I’m trying to do is avoid typing out a bunch of different select statements. I tried looping through syscolumns.colorder with a counter, but I’m executing select statements on things I don’t want.

Any ideas?

2

In your example, Do you expect your query to return a row when (LawnMower or Rake)='Y ?

If so, perhaps you could prerefix them with something like LAWN_mower and LAWN_Rake.

Then you could iterate through syscolumns where name like ‘LAWN_%’

3

Need a few more details to be able to help. What exactly don’t you want?

Missed the “lawn implement” when I read it first. To avoid hard-coding specific columns, need to know more about real columns to help find a simple solution.

4

A few more details…

I really don’t want to do a select, I want to do an update to a separate table. UPDATE MyOtherTable SET something=something WHERE LawnImplement = (my variable). In other words, some columns in MyTable have a corresponding row in MyOtherTable.

There is one thing that my desired columns have in common - in syscolumns, they all have a length of 1.

What I’d really like is some sort of property like MyTable.Column(x).Name, but I think I’m in the wrong language for that.

5

This sort of question/request most often indicates a database that isn’t properly normalized - the need to select values from a dynamic choice of similar columns usually means those columns should all be combined in a child table, with another column to indicate the identity of the value being shown - so that the matter of selecting the appropriate values can be performed with parameterised criteria, not variable SQL code.

I fully appreciate this probably isn’t at all helpful, but I felt the need to say it anyway. Please feel free to tell me to get lost.

6

Actually, the structure is fine, I think, but what I have to do is turn the data on its side. Even without being able to do what I asked about, it’s getting out of hand and unworkable. And there’s probably an easy solution that I’m just not seeing.

Maybe if I post what the actual situation is, it can be easier to understand.

It’s a table of postop patients and their complications. The actual table looks more like this:

CREATE TABLE Complications (
MedRecNum varchar(8),
Pneumonia char(1),
PneumoniaDate datetime,
PneumoniaNotes text,
RenalFailure char(1),
RenalFailureDate datetime,
RenalFailure Notes text
)

That shows just two complications, there are actually about 20 of them. If a patient had one, they get a value of ‘Y’ in the char(1) field. So Becky Smith, MRN 12345678 got pneumonia on February 12th, and Tom Jones, MRN 87654321 got renal failure on March 3rd. The table would return this:

‘12345678’|‘Y’|‘2/12/09’|‘some text’|null|null|null
‘87654321’|null|null|null|‘Y’|‘3/3/09’|‘some text’

This table will link on MRN to another table that has data on the surgery, including the surgery date. What I need to do is return the number of cases of pneumonia and renal failure in various time buckets. How many patients had surgery in 2008 and got pneumonia within 30 days of surgery? How many got it after 30 days? How many cases were there like that compared to total cases, percentage-wise? Same questions for 2007, 2006, etc.

And I need that all on one row. The next row will be all the same stuff but for renal failure, then more rows for stroke, sepsis, etc.

The good news is that I can put all of this into a stored procedure, so I can update to my heart’s content.

Are we looking at a pivot table here?

7

I haven’t done any serious database work since the mid-90’s using FoxPro. But the above reminds me of one of the coolest features of FoxPro – the “&” command (called “macro” in the manual). Basically, it performs a textual substitution (or, more explicitly, an “interpret the contents of this variable as a literal” command).

We used it heavily in combination with the “copy struct” command, which produced a 4-column “structure file” .dbf consisting of: fieldname, fieldtype, fieldlen, decimal places. In combination, the two provide “reflective computing” capabilities (similar in a rudimentary way to the java.lang.reflect.Field object in Java, but applied to database fields). Naturally, one could go the other way also, using the “create table” command, which took a 4-column structure file to create a table.

To use an explicit example, assume the record under consideration in the current table had the value “Toro” in the Lawnmower field. If you set the string variable field_nm to “Lawnmower”, you could set a variable field_val to the contents of the Lawnmower field using the statement field_val = &field_nm (the equivalent of field_val = Lawnmower.value() or field_val = “Toro” in this case).

I’m not sure if the above is any use to you, but perhaps it’ll lead somewhere. And I’d note again that I’ve not done any serious database work in years – nowadays, there may very well be better (or, at least, more common) ways to do the above.

8

That’s definitely a normalization problem. This is one of the most common mistakes in creating a database. If you’re doing much db work, read up on normal forms.

That’s why your setup is getting out of hand, it’s built wrong. See below.

You can probably use a pivot table for what you’re wanting on this job, although you’d have to update your code anytime you added new columns. (SQL doesn’t do dynamic columns in a PIVOT.) You can Google up tons of instructions for creating pivots.
Approximate code only, off the top of my head.

CREATE TABLE SURGINFO
(recordID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, MRN char 10, SurgDate datetime, …)

CREATE TABLE CONDITIONS
(conditionID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ConditionName varchar 50, …)

CREATE TABLE SURGCOMPLICATIONS
(complicationID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, recordID int REFERENCES SurgInfo(recordID), conditionID int REFERENCES Conditions(conditionID), conditionDate datetime, conditionNotes text, …)

INSERT SurgInfo (MRN, SurgDate) VALUES (‘123456’, ‘1/1/2009’)
INSERT SurgInfo (MRN, SurgDate) VALUES (‘987654’, ‘7/31/2009’)

say those have PK identity values 1 & 2

INSERT Conditions(ConditionName) VALUES (‘Pneumonia’)
INSERT Conditions(ConditionName) VALUES (‘Renal Failure’)
INSERT Conditions(ConditionName) VALUES (‘Infection’)

so those have identity values 1, 2, 3

Now you use that info to fill SurgComplications

INSERT SurgComplications(recordID, conditionID, conditionDate, conditionNotes)
VALUES(1,1,‘1/15/2009’,‘surg record 1, pneumonia, 15 days after surg’)
INSERT SurgComplications(recordID, conditionID, conditionDate, conditionNotes)
VALUES(2,2,‘6/01/2009’,‘surg record 1, renal, 6 months after surg’)
INSERT SurgComplications(recordID, conditionID, conditionDate, conditionNotes)
VALUES(2,1,‘10/15/2009’,‘surg record 2, pneumonia, 2 months after surg’)

The your query would be something like this, except a bit more complicated to pick up all your comparison fields. Google Common Table Expressions would be my best suggestion for writing these sorts of complex queries…

SELECT CN.ConditionName, count(SI.recordID)
FROM SurgInfo SI
inner join SurgComplications SC on SC.recordID = SI.recordID
inner join Conditions CN on CN.conditionID = SI.conditionID
WHERE SI.SurgDate between ‘1/1/2009’ and ‘12/31/2009’ and
DATEDIFF(‘dd’,SI.SurgDate,SC.ConditionDate) <= 30
GROUP BY CN.ConditionName
ORDER BY CN.ConditionName

That would get you a row for each Condition, with a count of the number of surgeries, for surgery dates in 2009 that got a complication within 30 days.

You would get one guy counted twice (for pneumonia & renal failure) unless you set up your query to prevent it. Depends on how you’re wanting your count, by condition or by person.

9

I completely agree with Redtail and Mangetout, you have a DB normmalization problem. If it’s legacy, it’s probably because with older generation servers (and I’m talking like over 10 years old, ancient hardware in today’s terms), they simply couldn’t handle relational logic as well and reports could take hours to run using stuff like redtail outlined.

More modern systems, however, are plenty robust enough to provide what you need, and using normal forms will make maintaining your DB much easier.

10

Agree - complications should be a vertical list of values, not a horizontal array of columns - that way, if and when a new kind of complication arises (say, ‘surgical equipment left inside’) it only needs to be added to the list - it doesn’t necessitate a change in the table structure.