Query to retrieve row level value as Column wise

Question

Query to retrieve row level value as Column wise

Manokarnan

SSC Eights!

Points: 852
More actions
April 20, 2006 at 11:19 pm

#114433

Hai,

   I have table "test" with the value as,

              Sno     Colour
                1       Red
                2       Blue
                3       Green

Now i want the result as

             Column1 Column2 Cloumn3
             Red        Green      Blue
(i.e) i need a Query to retrive the value of Column colour as Column wise.
Thanks,

ManoKarnan

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Interested to see for what purpose you want this format.

Anytime I want to display the data in a different format, I use the application that the data will ultimately be published in.

That saying, I use excel for this sort of job. Do a normal select statement and copy and paste the data into an excel spreadsheet. Then copy the block of data in excel and choose paste special. Click on the transpose check box, and volia the data appears as per your request.

If you really need to see the data in the format within query analyzer, I have seen various posts on this site to do this.

Search this site for 'transpose columns and rows' and there are a number of posts.

------------------------------
The Users are always right - when I'm not wrong!

Manokarnan SSC Eights! Points: 852 More actions · Answer 2

Hai,

I need to see the data in the format within query analyzer. I need a Query for that.

Thanks,

ManoKarnan

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Do a search for Case,

There are a million examples of this.

create table #mytable (Sno int, Colour varchar(10))

insert into #mytable (Sno, Colour)

select 1,'Red' union

select 2, 'Blue' union

select 3, 'Green'

select min(case sno when 1 then Colour end) as Column1,

min(case sno when 2 then Colour end) as Column2,

min(case sno when 3 then Colour end) as Column3

from #Mytable

drop #Mytable

Manokarnan SSC Eights! Points: 852 More actions · Answer 4

Hai,

The table which i given is just an example in my real time scenario there may be thousands of records. In such cases this case statement is not comfortable to do. I need a dynamic query to retrieve the results set.

Thanks,
ManoKarnan

RyanRandall SSChampion Points: 13623 More actions · Answer 5

This might be useful...

http://www.sqlteam.com/item.asp?ItemID=2955

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.