September 17, 2015 at 12:23 pm
Hi,
I am looking for a solution for the below problems:
I want to concatenate the column_names with the respective values.
create table #test1 (Id int,Name varchar(10),Country varchar(10))
insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')
select * from #test1
i have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:
ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA
Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamicselect ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1) ss
Thanks a lot:-)
September 17, 2015 at 12:41 pm
You can leverage the INFORMATION_SCHEMA.COLUMNS system table, which contains one row for each table and column in the database, to build up a SQL select statement into a variable.
To execute dynamic SQL statement, you can leverage the EXEC( <@SQLString> ) function.
For example:
set @SQL = 'select a, b, c from MyTable';
EXEC( @SQL );
Between these two things, you should be able to do what it is you want.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 17, 2015 at 5:09 pm
Thanks a lot for replying. I was already using that.Somehow it is not giving what i wanted. I am trying different things now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply