September 8, 2011 at 7:41 am
Hi I got a scenario where i should iget all the columns and their respective vaules in that rows to one column
Here is the Example for that
I have one table Called NAME
Create table Name
(
Name varchar(5),
Geo int,
His int,
Sci int
)
Insert into Name
Values('saz',1,null,2)
Insert into Name
Values('jack',null,2,1)
Insert into Name
Values('jhon',3,1,1)
Select * from NAME
Name Geo His Sci
saz 1 NULL 2
jack NULL 2 1
jhon 3 1 1
Now I want to have the result in the form of
Name--------SUBJECT
Saz----------Geo:1, Sci:2
Jack---------His:2, Sci:1
Jhon---------Geo:3, His:1, Sci:1
I can try to get all rows into one column but how to get the column names also? can some one help me out with this please
Thank You...
September 8, 2011 at 8:22 am
You could join with the information_schema.columns to get those names, but it might be easier to do it manually if you know the columns and are writing the code:
select name + ' Geo: ' + Geo + ' Sci: + Sci
from MyTable
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 8, 2011 at 11:50 am
But i'm getting an error when i execute this code
Query:
select name , ' Geo: ' + Geo + ' Sci: ' + Sci
from name
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' Geo: ' to data type int.
September 8, 2011 at 12:23 pm
tripri (9/8/2011)
But i'm getting an error when i execute this codeQuery:
select name , ' Geo: ' + Geo + ' Sci: ' + Sci
from name
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' Geo: ' to data type int.
Cast the int columns to varchar in the concatenation. i.e.
select name , ' Geo: ' + cast(geo as varchar(100)) + ' Sci: ' + cast(sci as varchar(100))
from name
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply