0

i want to achieve something like this:

#Statement for selecting specific Columns
Select (Select column_name from information_schema.COLUMNS where column_name like 'code%' and table_name like 'databasewithmanymanycolumns') from databasewithmanymanycolumns;

(This example isn´t working, it´s just there for explaining what i want to achieve) I want to Select Columns with the Name Like in a Subquery without explicitely have to name every column, because the table has simply too much to name everyone explicitely.

Is there kind of Statement that could achieve this? Any workaround with other statements?

Thank you very much for your help, kind regards, SirSandmann

SirSandmann
  • 300
  • 5
  • 13
  • Ah I see what you're doing. You have to use dynamic SQL to accomplish this. You can't embed a select statement in the select statement like this without it. – xQbert Dec 03 '15 at 17:20
  • 1
    This kind of problem can be symptomatic of poor design – Strawberry Dec 03 '15 at 17:25
  • Probably you are looking for something similar to this http://stackoverflow.com/questions/18702203/select-columns-from-one-table-based-on-the-column-names-from-another-table – Naruto Dec 03 '15 at 17:26
  • It is, but I didn´t design this one, so I have to deal with it. I think redesign / structure the Database is the best solution, thanks guys! – SirSandmann Dec 03 '15 at 17:39

1 Answers1

1

The only way to do what I think you're trying to do is with dynamic SQL; return the results of the first query and use it to build up a second query.

By all means do that by hand to write the query in the first place - I do routinely in situations where there's a very wide table I need to return, it's much quicker and less error prone.

But I certainly wouldn't do what I think you're trying to do at runtime in a live application; it'd open up a world of performance and maintenance problems. Write the queries you'll actually need and save them for use, or refactor the database to remove this rather unusual requirement, or rethink what you're actually trying to do.

If I've misunderstood your requirement please feel free to expand your question and we can all try again :-)

eftpotrm
  • 2,241
  • 1
  • 20
  • 24