Resultset to display in column Format

Question

Post reply

Resultset to display in column Format

Snehal-162112

SSC Enthusiast

Points: 144
More actions
July 29, 2004 at 3:13 am

#102134

Hi all,
I need to display the resultset returned into column list.
For eg. i have a table Country_Master with columns id and name the values are
id Name
1 America
2 india
3 Singapore
4 Japan
5 Australia
Now if my query is
select Name from country_master where id < 3
then it sould return only one record in following format
Name_1 Name_2
America India
Means, usually the results are returned into rows but i need to conver the results in to columns.
If my query is
select Name from country_master where id > 2 and id < 6,
then it sould return only one record in following format
Name_1 Name_2 Name_3
Singapore Japan Australia
Thanks,
Snehal

Viewing 10 posts - 1 through 9 (of 9 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Sounds like the following may work:

SELECT [Name], '1900-01-01' ProcessedDtTm INTO #tmpTable

FROM Country_Master

WHERE [id] < 3

Then you would need to "walk" the #tmpTable for the 1st instance WHERE the record was not processed and put into a @Var. Then UPDATE the temp table and repeat. OR you could create a CURSOR and walk that instead.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Snehal-162112 SSC Enthusiast Points: 144 More actions · Answer 2

Hi Thanks,

But the issue is i want to write a query to fetch the records in desired format. is that possible ?

Regards,

Snehal

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

As you walk the temp table and build the @Var you can build in your formatting as you go. Then you could EXPORT the HEADER and DETAIL information

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Garen Bagdasarian SSC Enthusiast Points: 145 More actions · Answer 4

Hi,

What about this one...

declare @sqlcol as nvarchar(4000)

select @sqlcol=''

select @sqlcol = @sqlcol + ' ' + rtrim(Name ) from country_master where id < 3

print @sqlcol

Snehal-162112 SSC Enthusiast Points: 144 More actions · Answer 5

hey thanx for the reply. it seems i can do it with these.

Actually i want a bit complicated output....

I want to write a query which returns null or empty string even if there is no data in the table for corresponding where clause in the qurey.

For e.g. if i query

select Name from country_master where id > 3 and id < 8

then the out put will be

Name_1 Name_2 Name_3 Name_4

Japan Australia NULL NULL

I need to do further processing on the result set. so it will be better if i get a resultset as a ouput.

Regards,

Snehal

Garen Bagdasarian SSC Enthusiast Points: 145 More actions · Answer 6

declare @sqlcol as nvarchar(4000)

select @sqlcol=''

select @sqlcol = @sqlcol + ' ' + COALESCE (rtrim(name), 'NULL') from country_master where id < 3

print @sqlcol

Snehal-162112 SSC Enthusiast Points: 144 More actions · Answer 7

Hi,

The query doesnt return desired output.

If i try this,

declare @sqlcol as nvarchar(4000)

select @sqlcol=''

select @sqlcol = @sqlcol + ' ' + COALESCE (rtrim(name), 'NULL') from country_master where id > 3 and id < 8

print @sqlcol

Then it is returning only

Japan Australia,

instead i want output Japan Australia NULL NULL

Garen Bagdasarian SSC Enthusiast Points: 145 More actions · Answer 8

Hi,

In this case you have to create a TEMP table

declare @iStartID int,

@iEndID int,

@sqlcol as nvarchar(4000)

select @iStartID = 3, @iEndID = 8

Create Table #Dummy (ID int)

while @iStartID<@iEndID-1

Begin

select @iStartID = @iStartID+1

insert #Dummy select @iStartID

end

select @sqlcol=''

select @sqlcol = @sqlcol + ' ' + COALESCE (rtrim(name), 'NULL') from #Dummy left join country_master on #Dummy.id=country_master.id

print @sqlcol

drop table #Dummy

Hope it will help!

Snehal-162112 SSC Enthusiast Points: 144 More actions · Answer 9

Hi there,

yep it works....Thanks,

Is it possible to write a single query which returns the resultset in desired format......

Regards,