Place results in Colmn rather than rows

I have a few tables that i need to run a query on and instead of having them appear in multiple rows how do i return teh results in columns instead.

eg: System Name

1 Mr A

1 Mr B

2 Mr C

2 Mr D

INTO System Name1 Name2

1 Mr A Mr B

2 Mr C Mr D

SELECT CASE WHEN THEN ELSE END

Adamus

|||

SELECT CASE Name

WHEN System_ID = '1',

THEN

Name2

ELSE

Name3

END

Not sure i get you?

|||declare @.table table
(
[System] int,
[Name] varchar(5)
)

insert into @.table
select 1, 'Mr A' union all
select 1, 'Mr B' union all
select 2, 'Mr C' union all
select 2, 'Mr D'

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]
|||

Thanks

The names Mr A, Mr B etc will be in the hundreds so don't really fancy typing them all out. There could be up to 4 or 5 different names per system.

i have tried to adapt to this but doesn;t work:-

declare @.table table

(

[System] int,

[Name] varchar(5)

)

insert into @.table

select System_ID, (firstname + ' ' + surname) as Name union all

select System_ID, (firstname + ' ' + surname) as [Name 1] union all

select System_ID, (firstname + ' ' + surname) as [Name 2]

where system = 1

From ((((dbo.System as S..........followed by my joins....

Select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])

from @.table a

group by a.[System]

How do i do this when i need to search for the criterea?

|||the table variable is for demonstrating the script.

use the query and change to your actual table name.

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]|||

ok . got it working partially,

The Min and Max just returns 2 results? Some have 3 or 4 names?

|||do this in your front end application. It can be done in T-SQL but it will not be clean