subquery? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subquery?

Author

Topic

danbweb
Starting Member

7 Posts

Posted - 2007-08-21 : 07:07:54

Have a table:

ID M1 M2 M3 M4 M5 M6
-------------------------------------
1 A1 B3 B4 D5
...
...
...

For any record, I am trying to pull out only the values in 6 specific columns (M1-M6) which correspond to a variable. If the value in any column equals the varaible, it should be included, otherwise it shouldn't

For example:

If x = "B" and ID = 1

I want to pull the record for ID #1 and return the two columns M2 and M3, because Left(ColumnX,1) = x (which has the value of "B") for both those columns. Columns M1, M4, M5, M6 would not be returned

I tried to code a subquery, but it didn't work

Thanks for any help

Dan B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 07:13:17

You need dynamic sql for this, see http://www.sommarskog.se/dynamic_sql.html

I tell you right now, this is bad design.

E 12°55'05.25"
N 56°04'39.16"

danbweb
Starting Member

7 Posts

Posted - 2007-08-21 : 08:18:50

Absolutely - but unfortunately, I am stuck with a legacy-type db. Can't make structure changes to it

quote:
Originally posted by Peso

You need dynamic sql for this, see http://www.sommarskog.se/dynamic_sql.html

I tell you right now, this is bad design.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 08:42:31

I know. You HAVE to use dynamic sql for this task.
Not only because it is a legacy system, but because the design is flawed and you want different number of columns depending of a parameter.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources