FORMAT QUERY RESULTS

[jrc1]

I am running multiple select statements in Query Analyzer. I would like to wrap the output of ONE of the the queries. For wxample if the table output first names like
Jeff
John
Joe
Jim
I would like to format the output like
Jeff, John, Joe, Jim
or
Jeff John
Joe Jim
How can I format the select statement results to be shown in above format?

 

[BobWman]

How about this - assuming the data is in a table called Names and the Field is FirstName.

Select Name1=N1.FirstName, Name2=N2.FirstName, Name3=N3.FirstName
From Names N1
Inner Join Names N2 On N1.(Something) = N2.(Something)
Inner Join Names N3 On N1.(Something) = N3.(Something)

This may not be what you want, but hopefully it will give you a hint.

 

[jrc1]

Thanks for the reply. I believe the mentioned script would produce three identical columns with the same values. I don't want to duplicate values. I want the values spread over a numerous columsn and not duplicated.

 

[BobWman]

You're right - sorry for the dumb response.

This will work for 2 columns of names, and can be expanded for additional names.

Create Table #T1(
ID Int,
Name Char(15)
)
Create Table #T2(
ID Int,
Name Char(15)
)
Create Table #T3(
ID Int,
Name1 Char(15),
Name2 Char(15)
)

Insert into #T1
Select
ID=(ID-1)/2+1,
Name = Case
When ID%2=1 then
Firstname
Else
Null
End
From
NameTable

Insert into #T2
Select
ID=(ID-1)/2+1,
Name = Case
When ID%2=0 then
Firstname
Else
Null
End
From
NameTable

Insert Into #T3
Select
T1.ID,
Name1 = (Select Name From #T1 Where ID = T1.ID and Name is not null),
Name2 = (Select Name From #T2 Where ID = T1.ID and Name is not null)
From
#T1 T1
Inner Join #T2 T2 On T1.ID=T2.ID
Group By T1.ID

Select * From #T3

Drop Table #T1
Drop Table #T2
Drop Table #T3

 

[BobWman]

I should have mentioned in previous response that the ID field in my source table (with the names) is an identity field.

 

[rajeessh]

these statements will provide u better results
Correct me if i am worng

declare @names nvarchar(3000)
set @names = ''
select @names = COALESCE(@names + ',',@names) + NameField from NameTable
if left(@names,1) = ',' select @names = substring(@names,2,len(@names)-1)
select @names

NameTable referes to Table Name
NameField refers to the field which holds name