April 18, 2011 at 9:24 am
Dear All,
I need to write a query which brings me the data on the columns matched with column names.
For example,
id name surname age
1 John Smith 20
I need a query which brings me:
id:1;name:John;surname:Smith;age:20
Is it possible?
Thanks in advanced
April 18, 2011 at 9:45 am
May I ask why you need this?
April 18, 2011 at 9:52 am
Is this you need
create table #Temp (id int,name varchar(30), surname varchar(30) ,age tinyint)
insert into #Temp values (1, 'John', 'Smith', 20)
insert into #Temp values (2, 'Pen', 'Drive', 14)
Select COL_NAME(OBJECT_ID('#Temp'), 1)+':'+convert(varchar,Id)+';'+COL_NAME(OBJECT_ID('#Temp'), 2)+':'+convert(varchar,name)
+';'+COL_NAME(OBJECT_ID('#Temp'), 3)+':'+convert(varchar,surname)
+';'+COL_NAME(OBJECT_ID('#Temp'), 4)+':'+convert(varchar,age)
from #Temp
where name='John'
drop table #Temp
use COL_NAME ,OBJECT_ID function
Thanks
Parthi
April 18, 2011 at 10:04 am
parthi-1705 (4/18/2011)
Is this you needcreate table #Temp (id int,name varchar(30), surname varchar(30) ,age tinyint)
insert into #Temp values (1, 'John', 'Smith', 20)
insert into #Temp values (2, 'Pen', 'Drive', 14)
Select COL_NAME(OBJECT_ID('#Temp'), 1)+':'+convert(varchar,Id)+';'+COL_NAME(OBJECT_ID('#Temp'), 2)+':'+convert(varchar,name)
+';'+COL_NAME(OBJECT_ID('#Temp'), 3)+':'+convert(varchar,surname)
+';'+COL_NAME(OBJECT_ID('#Temp'), 4)+':'+convert(varchar,age)
from #Temp
where name='John'
drop table #Temp
use COL_NAME ,OBJECT_ID function
Why all the function calls?
select
'id: ' + id + ';' +
'name: ' + name + ';' +
'surname: ' + surname + ';' +
'age: ' + age
from
#temptable;
April 18, 2011 at 10:09 am
Lynn Pettis (4/18/2011)
Why all the function calls?
select
'id: ' + id + ';' +
'name: ' + name + ';' +
'surname: ' + surname + ';' +
'age: ' + age
from
#temptable;
Just to tell him/her that we can take the column name by using function
Nothing wrong in learning new.Just harcoding is not the excate solution.In future if there is any change in the column names then you no need to worry about it.
Thanks
Parthi
April 18, 2011 at 10:11 am
parthi-1705 (4/18/2011)
Lynn Pettis (4/18/2011)
Why all the function calls?
select
'id: ' + id + ';' +
'name: ' + name + ';' +
'surname: ' + surname + ';' +
'age: ' + age
from
#temptable;
Just to tell him/her that we can take the column name by using function
Nothing wrong in learning new.Just harcoding is not the excate solution.In future if there is any change in the column names then you no need to worry about it.
Parthi, can you modify your code so that it doesn't contain any hard-coded column names?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 10:12 am
Lynn Pettis (4/18/2011)
Why all the function calls?
Because I won't know column names at run time. Even I won't know table name. Table name will passed as a parameter to my system and I will run a query to receive all data with it's column names, will convert it as a comma separated message and send back.
April 18, 2011 at 10:16 am
Hi parthi-1705,
Yes, that's exactly what I needed but when I run your query I couldn't get columnNames:data pairs.
Let me do further tests, I'll let you know.
Thank you very much
April 18, 2011 at 10:22 am
parthi-1705 (4/18/2011)
Lynn Pettis (4/18/2011)
Why all the function calls?
select
'id: ' + id + ';' +
'name: ' + name + ';' +
'surname: ' + surname + ';' +
'age: ' + age
from
#temptable;
Just to tell him/her that we can take the column name by using function
Nothing wrong in learning new.Just harcoding is not the excate solution.In future if there is any change in the column names then you no need to worry about it.
If you add, change, or drop a column you have to go in and change the query any way, why the extra overhead of the function calls? Doesn't make sense to me, nor does this request at this time.
April 18, 2011 at 10:27 am
You are going to need to use dynamic sql to generate the query you need to run using system tables to identify the column names for the table requested.
I don't have SQL Server available to me here at work, so I can't help until later this evening.
April 18, 2011 at 10:28 am
Hmm, yes, I can't use column names in the query no matter I'm doing it with function calls or not.
As I've written in my previous post, I won't know the table column names on run time.
Let say, I have A table with a,b,c columns and X table with x,y,z columns.
When I pass A as parameter, I should get a:john;b:smith;c:20
When I pass X as parameter, I should get x:London;y:England;z:UK
April 18, 2011 at 10:31 am
Lynn Pettis (4/18/2011)
You are going to need to use dynamic sql to generate the query you need to run using system tables to identify the column names for the table requested.I don't have SQL Server available to me here at work, so I can't help until later this evening.
I have a couple of days until I start related task, it's not so urgent at the moment.
But thanks anyway, I wasn't expecting to get responses so quickly guys.
April 18, 2011 at 11:07 am
Lynn Pettis (4/18/2011)
parthi-1705 (4/18/2011)
Lynn Pettis (4/18/2011)
Why all the function calls?
select
'id: ' + id + ';' +
'name: ' + name + ';' +
'surname: ' + surname + ';' +
'age: ' + age
from
#temptable;
Just to tell him/her that we can take the column name by using function
Nothing wrong in learning new.Just harcoding is not the excate solution.In future if there is any change in the column names then you no need to worry about it.
If you add, change, or drop a column you have to go in and change the query any way, why the extra overhead of the function calls? Doesn't make sense to me, nor does this request at this time.
I have stated as change in column name ie instead of Name it is said as Firstname then it is going to return Firstname there is no change in positition.If there is any increase or decrease in the table column then your's is correct,but if not only change in the field name then the function will be solution instead of hardcoding.
Thanks
Parthi
April 18, 2011 at 11:35 am
parthi-1705 (4/18/2011)
Lynn Pettis (4/18/2011)
parthi-1705 (4/18/2011)
Lynn Pettis (4/18/2011)
Why all the function calls?
select
'id: ' + id + ';' +
'name: ' + name + ';' +
'surname: ' + surname + ';' +
'age: ' + age
from
#temptable;
Just to tell him/her that we can take the column name by using function
Nothing wrong in learning new.Just harcoding is not the excate solution.In future if there is any change in the column names then you no need to worry about it.
If you add, change, or drop a column you have to go in and change the query any way, why the extra overhead of the function calls? Doesn't make sense to me, nor does this request at this time.
I have stated as change in column name ie instead of Name it is said as Firstname then it is going to return Firstname there is no change in positition.If there is any increase or decrease in the table column then your's is correct,but if not only change in the field name then the function will be solution instead of hardcoding.
Parthi - half of the column references in your script are hard-coded.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 7:55 am
Hi Guys,
Any update on this? 🙂
Regards
ilker
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply