Turning a recordset into one column with multiple rows

[GKWilly]

Hi )

What I'm trying to do is turn a recordset into one column of data with mutliple rows ie:

SourceData =
ID Firstname Lastname Country
1 Martin Anders England

and I need

Col1
Martin
Anders
England

I've tried to write it as a cursor but my experience of cursors is very limited and It won't even parse!

Can anyone help me please!!

My cursor is below.

declare @var1 varchar(50),declare @var2 varchar(50),declare @var3 varchar(50)


declare cursor cu for
select Firstname, Lastname, Country from tableb
open cu
fetch next into @var1, @var2, @var3
while @@fetch_status = 0

begin
fetch next into @var1, @var2, @var3
end

close cu
deallocate

I Hopesomeone can help me.

Many Thanks

 

[SQLSister]

Are you doing this for a report? It is best to let your report writer handle formating issues. This is not a task that can be done easily or efficiently in SQL server.

Questions about posting. See faq183-874

 

[GKWilly]

Unfortuantley I am the report writer and this is something I 've been asked to do not sure why they need, ours is not to reason why!
Any ideas an why my cursor wont run?

 

[dbomrrsm]

declare @string varchar (500)
DECLARE @CR AS Char(2)
SET @CR=Char(13)+Char(10)
set @string = 'col1result '+@CR+'Col2result '+@CR+'Col3result'
PRINT @String

If you can work with this in some way it may be a solution or at last point you in the right direction.

[blue]DBomrrsm[/blue]

 

[SQLSister]

I didn't mean the report writer person, I meant the report writing software - Crystal Reports, for instance.

Y0ur cursor syntax is incorrect.

declare @var1 varchar(50), @var2 varchar(50), @var3 varchar(50)


declare  cu cursor for 
    select Firstname, Lastname, Country from tableb
open cu
    fetch next into @var1, @var2, @var3
while @@fetch_status = 0

begin
fetch next into @var1, @var2, @var3
end

close cu
deallocate cu

I don't see where this will get you what want though.

Questions about posting. See faq183-874