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

  • 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

    Thursday, November 5, 2015 6:32 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 5, 2015 8:41 PM
  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:45 AM
  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 10:11 PM

All replies

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

    Thursday, November 5, 2015 6:47 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 5, 2015 8:41 PM
  • 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

    Thursday, November 5, 2015 11:58 PM
  • 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

    Friday, November 6, 2015 1:43 AM
  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:45 AM
  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 10:11 PM