Is it Possible to convert varchar to column and fetch the column from another table

Question

User1624600859 posted

Hi all,

I have following output

id    c1     c2          c3             c4     c5

2    130     2          Creditor  8254   ff2
3    130     3          External   8254   ff3

ff2 and ff3 are columns of another table 2

table 2

ff2     ff3

test   test2

What i want is

id    c1     c2          c3             c4     c5

2    130     2          Creditor  8254   test   
3    130     3          External   8254   test2

Thanks in advance

Answer 1

User1577371250 posted

Select id, c1, c2, c3, c4, c4 AS c5 FROM table1 ...;

Answer 2

User1644755831 posted

Hello,

Please try this.

DECLARE @TABLE1 AS TABLE
(
  id int,
  c1 int,
  c2 int,
  c3 varchar(25),
  c4 int
)

DECLARE  @FF1 AS TABLE
(
 c5 varchar(25)
)
DECLARE  @FF2 AS TABLE
(
 c5 varchar(25)
)

INSERT @TABLE1 
SELECT 1,130,2,'Creditor',8254
UNION ALL 
SELECT 2,130,3,'External',8254

INSERT @FF1 SELECT 'test'
INSERT @FF2 SELECT 'test2'

select 
T.*,
CASE WHEN id = 1 then A.c5 WHEN id = 2 then B.c5 END c5
from 
@TABLE1 T CROSS JOIN @FF1 A CROSS JOIN @FF2 B

Result

id	c1	c2	c3	        c4	c5
1	130	2	Creditor	8254	test
2	130	3	External	8254	test2

With Regards,

Krunal Parekh

Answer 3

User1624600859 posted

Thanks 

but here ff1 ff2 are not fixed like in future the table 2 can also contain ff3 ff4 and so on

we cannot use static variable for the above mentioned columns ..and if we do so then each time we have to modify the sql code if any new column is created in Table 2

Answer 4

User1644755831 posted

Hello,

It depends I just showed you an example. You might use pivot or union all to achieve the same. I can not give solid advise because I don't know your structure and requirement.

With Regards,

Krunal Parekh

Answer 5

User753101303 posted

Hi,

It really depends what you are trying to do. Not sure to get your requirements but you could also add rows to a table and fetch the data based on this row id rather than adding columns to a table (which seems to have a single row?) and fetching data based on the column name.

This way you don't have any structure change and you don't need to build a dynamic query etc...

Answer 6

User-62323503 posted

You need to implement dynamic unpivot in this case

Refer below post for your reference.

http://www.itdeveloperzone.com/2011/04/columns-to-rows-in-sql.html

Solution:

create table tab1 (
  id int, c1 int, c2 int, c3 varchar(25), c4 int, c5 varchar(25)
)

create table tab2(
 ff2 varchar(25), ff3 varchar(25)
)

insert into tab1
values (1,130,2,'Creditor',8254, 'ff2'), (2,130,3,'External',8254, 'ff3')

insert into tab2
values ('test', 'test2')

declare @ColumnList varchar(max),
        @Query      varchar(max),
        @ColumnName varchar(100),
        @TableName  nvarchar(100)

set @TableName = 'tab2'
select @ColumnList = Stuff(o.list, 1, 1, '')
from  ( 
	select ',' + name from syscolumns where  id = Object_id(@TableName) for xml path('')
) o(list)

SET @Query = 'select  id , c1 , c2 , c3 , c4 , r.value as	c5 
from	tab1 t
inner join ( 
	select keyname, value
	from   (
		select * from ' + @TableName + ') as t1  unpivot ( value for keyname in (' + @ColumnList + ')
	) t
) r on t.c5 = r.keyname'
EXEC(@Query)

drop table tab1
drop table tab2