Dynamically selected columns with column switch option

Hi,
my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. This is easier
explained with the following statements one can paste into Query
Analyzer:
declare @.sql nvarchar(4000), @.cols nvarchar (500)
set @.cols = '
v1 = case when sel=1 then
v1a else v1b end,
v2 = case when sel=1 then v2a
else v2b end
'
create table ##temp_test (
v1a int,
v1b int,
v2a varchar(15),
v2b varchar(15),
sel bit
)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (1, 2, 'a1', 'b1', 0)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (3, 4, 'a2', 'b2', 1)
set @.sql = 'select ' + @.cols + 'from
##temp_test'
exec sp_executesql @.sql
drop table ##temp_test
Result:
v1 v2
-- --
2 b1
3 a2
This fits bots requirements: @.cols (which is stored in a table in the
real world application) holds the column names to be selected, and it
holds it in a way which also enables to switch between the a and b
version of the columns via the CASE statements. The performance is OK.
My problem: the real world query is a lot more complex, ##temp_test is
actually a table with about 80 columns and there are a lot of other
tables joined in.
Since I am doomed to not use more than 4000 characters for the dynamic
sql part (not using sp_executesql results in a huge performance
penalty in my scenario) this approach in the end works find without
all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
@.cols variable length will grow by factor 2-3, typically from around
1000 - 2000 chars to about 2000 - 6000 chars.
That's the end of the sp_executesql approach.
Maybe our design is wrong in the first place. We have tried to
alternate between the columns by using UNION to a View that contains
the alternate column, but ended up in quiet a performance hit with
more complex queries plus sometimes the SORT function would not work
anymore and the like.
Maybe I can shorten the
v1 = case when sel=1 then
v1a else v1b end
part somehow? Or in the best case someone knows a similar approach...
just better.
TIA for any comments!
Regards
DChttp://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"DC" <dc@.upsize.de> wrote in message
news:5b7bac12.0502250228.4d94de6c@.posting.google.com...
> Hi,
> my requirement is to dynamically select certain columns from a table
> and depending on a flag some columns must be swapped. This is easier
> explained with the following statements one can paste into Query
> Analyzer:
>
> declare @.sql nvarchar(4000), @.cols nvarchar (500)
> set @.cols = '
> v1 = case when sel=1 then
> v1a else v1b end,
> v2 = case when sel=1 then v2a
> else v2b end
> '
> create table ##temp_test (
> v1a int,
> v1b int,
> v2a varchar(15),
> v2b varchar(15),
> sel bit
> )
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (1, 2, 'a1', 'b1', 0)
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (3, 4, 'a2', 'b2', 1)
> set @.sql = 'select ' + @.cols + 'from
> ##temp_test'
> exec sp_executesql @.sql
> drop table ##temp_test
>
> Result:
> v1 v2
> -- --
> 2 b1
> 3 a2
> This fits bots requirements: @.cols (which is stored in a table in the
> real world application) holds the column names to be selected, and it
> holds it in a way which also enables to switch between the a and b
> version of the columns via the CASE statements. The performance is OK.
> My problem: the real world query is a lot more complex, ##temp_test is
> actually a table with about 80 columns and there are a lot of other
> tables joined in.
> Since I am doomed to not use more than 4000 characters for the dynamic
> sql part (not using sp_executesql results in a huge performance
> penalty in my scenario) this approach in the end works find without
> all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
> @.cols variable length will grow by factor 2-3, typically from around
> 1000 - 2000 chars to about 2000 - 6000 chars.
> That's the end of the sp_executesql approach.
> Maybe our design is wrong in the first place. We have tried to
> alternate between the columns by using UNION to a View that contains
> the alternate column, but ended up in quiet a performance hit with
> more complex queries plus sometimes the SORT function would not work
> anymore and the like.
> Maybe I can shorten the
> v1 = case when sel=1 then
> v1a else v1b end
> part somehow? Or in the best case someone knows a similar approach...
> just better.
> TIA for any comments!
> Regards
> DC|||I don't understand why you want to use dynamic SQL for this. Where is
the string in @.cols generated from? You say it comes from a table but
why put it in a table at all? Why can't you define views for these
different views of the data?
David Portas
SQL Server MVP
--|||>> my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. <<
You might want to get any book on **basic software engineering** and
look up the concept of cohesion in a code module.|||Thank you! Can you recommend a good book on this topic? I probably
focussed too much on the loose coupling aspects? I support your claim
for the art of programming, but then again: it's only T-SQL. Hacky
stuff! Wouldn't a real programmer avoid messing around with SQL anyway?|||Thanks David, views or pre-generated stored procs are an option. We are
in the process of upgrading a life application though, and wanted to
integrate this additional requirement (the CASE req.) with the least
effort possible.|||Thank you Vinod, great article with a wealth of dynamic sql info. Does
not provide an exact solution, but pointed out that using EXEC shoud
not make as much of a difference as I experienced.|||> Wouldn't a real programmer avoid messing around with SQL anyway?
Sure. Why go looking for a new peg when you can just keep bashing that
square one into a round hole? After all, tables are only arrays aren't
they?
David Portas
SQL Server MVP
--|||On 25 Feb 2005 05:52:23 -0800, David Portas wrote:

>Sure. Why go looking for a new peg when you can just keep bashing that
>square one into a round hole? After all, tables are only arrays aren't
>they?
Hi David,
A "real" programmer - isn't that a guy who needs nothing but 8 switches
and 8 LED's to program the 'puter? Who needs all that modern assembler
language mnemonics anyway, huh?
(Hmmm - am I showing my age now <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Can you recommend a good book on this topic? <<
Any of the classic by Yourdon, Constantine, DeMarco or Gane & Sarson
are a good place to start.
They would avoid dynamic SQL and do the order of presentation of the
columns in the front end. I have become a fan of the idea that one
team in the shop handles the database and writes all the SQL for
everyone, and the application developers make requests to that team.