CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
marcus.the.programmer
marcus.the.programmer

Reputation: 77

TSQL - Dynamic Column Name in Dynamic SQL?

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!

Upvotes: 1

Views: 2913

Answers (2)

HABO
HABO

Reputation: 15816

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, ... .

Upvotes: 2

Andomar
Andomar

Reputation: 238048

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)

Upvotes: 0

Related Questions