TSQL - Dynamic Column Name in Dynamic SQL?

Question

I have a "table A" with the following structure:

Act_Code  ACT_TYPE_1  ACT_TYPE_2   ACT_TYPE_3  ACT_TYPE_4
--------  --------   -----------   ----------  -----------
ACT1       A            NULL        NULL         NULL
ACT2       NULL         B           NULL         NULL
ACT3       NULL         NULL        C            NULL
ACT4       NULL         NULL        NULL         D
ACT1       A            NULL        NULL         NULL

As you can see, the ACT_TYPE data will always store into the field name which refers to the last digit of "Act_Code" fields: Eg. When Act_Code = "ACT1", the Act_Type is stored in field "ACT_TYPE_1" When Act_Code = "ACT2", the Act_Type is stored in field "ACT_TYPE_2" and so on...

Now, I want to read the data from the above "Table A" and insert into "Table B" which is having the following structure:

ACT    ACT_TYPE
----   --------
1        A
2        B
3        C
4        D

Question: *1. How can I add "dynamic column name" inside a select query?*

For example,

INSERT INTO Table_B (ACT, ACT_TYPE)
SELECT RIGHT(Act_Code,1), ## FROM Table_A

How can I handle the dynamic column name as per the symbol "##" above?

I've tried:

SET @sql = 'INSERT INTO Table_B (ACT,ACT_TYPE) '
SET @sql = @sql + 'SELECT RIGHT(Act_Code,1), '
SET @sql = @sql + '''ACT_TYPE_'' + RIGHT(Act_Code,1) FROM Table_A'
EXEC (@sql)

But it doesn't work!

Please help, thanks very much!

You don't need a dynamic query. You can use CASE within a query to pick the value of the correct column. — HABO
– HABO, Commented Jun 30, 2012 at 18:20

HABO · Accepted Answer · 2012-06-30 18:25:14Z

2

declare @Foo as Table ( Voot varchar(10), Plevny1 varchar(10), Plevny2 varchar(10) )
insert into @Foo ( Voot, Plevny1, Plevny2 ) values ( 'Thing1', 'a', 'A' ), ( 'Thing2', 'b', 'B' )
select SubString( Voot, 6, 1 ) as Vootette,
  case SubString( Voot, 6, 1 )
    when '1' then Plevny1
    when '2' then Plevny2
    else NULL end as Plevny
  from @Foo

Depending on your specific requirements you may need to parse a multiple digit integer from the controlling column's value, may want to handle a default output value, may want to check for the "other" values in the row being NULL, ... .

answered Jun 30, 2012 at 18:25

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marcus.the.programmer Over a year ago

Dear user92546, I need to use dynamic sql as my table name is dynamic, though the above sample looks plain. If case when is to be used, I have total of 16 columns name for the "ACT_TYPE_x" fields, hence I need to handle 16 times for dynamic columns? thanks.

HABO Over a year ago

@user1480519 - Yes, 16 whens and the optional else. With 16 columns you'll need to do a little more work to handle the one or two digit selector if you really need to parse it from the first column's value. If there are simply 16 distinct values in the first column then you can use them directly as in Andomar's answer.

Andomar · Accepted Answer · 2012-06-30 18:20:15Z

0

Looks like you can do that without dynamic SQL:

insert  Table_B
        (Act, Act_Type)
select  case Act_Code
        when 'ACT1' then 1
        when 'ACT2' then 2
        when 'ACT3' then 3
        when 'ACT4' then 4
        end
,       coalesce(ACT_TYPE_1, ACT_TYPE_2, ACT_TYPE_3, ACT_TYPE_4)

answered Jun 30, 2012 at 18:20

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

marcus.the.programmer Over a year ago

Hi Andomar, what i need here is to get the column name for ACT_TYPE_1 to ACT_TYPE_4, but all of those columns have data inside rather than "Null" value, hence I don't think coalesce work here. Any more ideas? thanks!

marcus.the.programmer Over a year ago

Thanks Andomar, I use Case XX When .. THEN and it worked! Thanks!

Collectives™ on Stack Overflow

TSQL - Dynamic Column Name in Dynamic SQL?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related