Select a column dynamically

Question

Post reply

Select a column dynamically

just:bi

Mr or Mrs. 500

Points: 517
More actions
July 13, 2009 at 5:32 am

#139805

Hi there, have some data set out as follows:
0 1 2 3 4 5
a x
b __x
c ___x
d _____x
e _______x
(Underscores are there just to force the x under the right column!)
An application we are introducing has an "extra details" table which is built like so. Basically, column 0 will be an indicator as to what category the row is and based on that category, you would need to look at a specific column to find the value. So to explain that better....if column 0 = A I need to look at column 1, if column 0 = D, I need to look at column 4.
What I want to do is set up a simpler view of this basically listing the the category and the value but the problem is it won't be just one category, there will be multiple categories in column 0. In my head the easiest option would be to concatenate all the columns and use a TRIM however, I don't think I have any guarantee that the "should be NULL" columns will be blank - plus I don't want to risk it!
So I was wondering if there was any way to dynamically select a column based on the value in column 0? Can I set up a query that says something along the lines of if column 0 = A then use column 1, if column 0 = B then use column 2 etc? This would need to be applied per row as the table will consist of multiple categories.
Hope I've explained that well enough...:-D

Viewing 13 posts - 1 through 12 (of 12 total)

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

Amit-1103237 SSC Enthusiast Points: 197 More actions · Answer 1

Still some clarification needed man....

means are u looking for a query that will accept the category and output the column dynamically

or something else ??????/////

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Is this the kind of thing you looking for?

DECLARE @MyTable TABLE

([col0] CHAR(1),

[col1] CHAR(2),

[col2] CHAR(2),

[col3] CHAR(2),

[col4] CHAR(2),

[col5] CHAR(2))

INSERT INTO @MyTable

SELECT 'a','xa',null,null,null,null UNION ALL

SELECT 'b',null,'xb',null,null,null UNION ALL

SELECT 'c',null,null,'xc',null,null UNION ALL

SELECT 'd',null,null,null,'xd',null UNION ALL

SELECT 'e',null,null,null,null,'xe'

SELECT * FROM @MyTable

SELECT

col0,

CASE col0

WHEN 'a' THEN [col1]

WHEN 'b' THEN [col2]

WHEN 'c' THEN [col3]

WHEN 'd' THEN [col4]

WHEN 'e' THEN [col5]

ELSE 'N/A' END as [Dynamic Col]

FROM @MyTable

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Amit-1103237 SSC Enthusiast Points: 197 More actions · Answer 3

I think this hard code insertion wont do if something like dynamic columns are to be retrieved... There must be some other effecient way to acheive the same...

Can you suggest some better way buddy...?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

I'm unclear, on why it needs to be dynamic?

are you saying that you never know how many columns there are in the table?

Secondly why is your table constructed this way.

Could you give me some more information into the actual business logic around this problem?

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Here is another solution.

This is based on two thing.

1 - Columns are not if not being used.

2 - The number of columns is known.

SELECT

Col0,

COALESCE(Col1,Col2,Col3,Col4,Col5)

FROM @MyTable

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

just:bi Mr or Mrs. 500 Points: 517 More actions · Answer 6

Christopher Stobbs (7/13/2009)
I'm unclear, on why it needs to be dynamic?
are you saying that you never know how many columns there are in the table?
Secondly why is your table constructed this way.
Could you give me some more information into the actual business logic around this problem?
thanks
Chris

Well it doesn't need to be to be honest, poor choice of words on my part....

Essentially, I think your first solution will probably work.

Why is the table constructed that way? No idea, the platform we are bringing in has some weird and wonderful quirks about it and this is just one, so I am attempting to make a simpler view of it.

The coalesce could work, but I can't be sure as to whether the other columns will be blank. Thanks for your suggestions will try each out and see which suits.

Still learning, so this has been useful, thanks 😉

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

Always happy to help 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

T4FF (7/13/2009)
...the platform we are bringing in has some weird and wonderful quirks about it and this is just one, so I am attempting to make a simpler view of it.

Heh... I'm officially not jealous. Sounds like a real PITA. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gatekeeper SSCommitted Points: 1633 More actions · Answer 9

T4FF (7/13/2009)
The coalesce could work, but I can't be sure as to whether the other columns will be blank. Thanks for your suggestions will try each out and see which suits.

You can also wrap each column in your coalesce with a NULLIF()

SELECT

Col0,

COALESCE(NULLIF(Col1,''),NULLIF(Col2,''),NULLIF(Col3,''),NULLIF(Col4,''),NULLIF(Col5,''))

FROM @MyTable

This will ensure you only get the first column that isn't truly empty.

/* Anything is possible but is it worth it? */

sgmunson SSC Guru Points: 110639 More actions · Answer 10

The problem with COALESCE is that you can only retrieve one value per row, and if I recall one of the OP's posts correctly, you might well need values from more than one column. Such a design is probably the very worst possible way to go about maintaining that kind of data. I have to wonder if it might be more practical to concatenate a series of ISNULL functions, a la:

SELECT ISNULL(cola,'') + ISNULL(colb,'') + ISNULL(colc,'') + ISNULL(cold,'') + ISNULL(cole,'') AS DynamicCol

-- rest of your T-SQL code goes here

Of course, this relies on there never being a value in a column where it "doesn't belong", based on the value in column 0. If you could guarantee that was always true, this might work for you.

Steve

(aka smunson)

:-):-):-)

THIS POST EDITED...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

anbillava Ten Centuries Points: 1146 More actions · Answer 11

Can we make this dynamic. I have similar problem. I need to check missing data varables in different tables dynamically.

Variables position in each table are fixed.

For e.g

1. Table1

idtable1 a1 a2 a3 valid

2. Table2

idtable2 b1 b2 b3 valid

2. Tablen

idtablen n1 n2 n3 valid

Now i need to give a report to user like this

TableName missingdatapoints count

Table1 a1,a3 2

Table2 b1 1

....................................................

......................................................

TableN n2,n3 2

Is it possible .. pls let me know..

Thanks in advance...

sgmunson SSC Guru Points: 110639 More actions · Answer 12

And here we have a near perfect example of why those kinds of designs are really bad ideas. No offense to anyone, but coding what this poster wants will; at a minimum, probably require dynamic SQL, and at worst, some kind of horrendous cursor. This is why proper database design is so incredibly important - it's designs like this that make getting information OUT of the database very difficult, or even impractical due to performance concerns.

The first question in this specific case is whether ALL the tables are identical from a data type perspective. Without that, each table would need separate processing. Also, exactly what constitutes the condition of "missing data" referred to? A null value? An empty string? An inappropriate value for a specific field? There's just not enough information in the post to know exactly what the poster wants.

Steve

(aka smunson)

:-):-):-)

anbillava (7/22/2009)
Can we make this dynamic. I have similar problem. I need to check missing data varables in different tables dynamically.
Variables position in each table are fixed.
For e.g
1. Table1
idtable1 a1 a2 a3 valid
2. Table2
idtable2 b1 b2 b3 valid
2. Tablen
idtablen n1 n2 n3 valid
Now i need to give a report to user like this
TableName missingdatapoints count
Table1 a1,a3 2
Table2 b1 1
....................................................
......................................................
TableN n2,n3 2
Is it possible .. pls let me know..
Thanks in advance...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)