Columns and rows into one Column

Question

Columns and rows into one Column

tripri

SSCommitted

Points: 1584
More actions
September 8, 2011 at 7:41 am

#247766

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
NameGeoHisSci
saz1NULL2
jackNULL21
jhon311
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...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

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

tripri SSCommitted Points: 1584 More actions · Answer 2

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.

toddasd SSCertifiable Points: 6298 More actions · Answer 3

tripri (9/8/2011)
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.

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.