Not a problem. What you need is a cursor to get the distinct list of your
names and generate a sql statement that might look something that looks
like this:

create table #temp
(
id int identity(1, 1),
names varchar(10),
items int
)

set nocount on
begin tran

insert into #temp (names, items)
select names, items from
(
select 'John' as names, 4 as items union all
select 'Tim', 3 union all
select 'John', 2 union all
select 'Tim', 1 union all
select 'Roger', 6 union all
select 'Roger', 7
) as x

commit
set nocount off

-- this is what you would generate.
-- note the column names and the cross joins.
-- as you loop the cursor you would concat a list of the columns.
-- as you loop the cursor you would concat a string of the tables to join
select distinct john.itmes as john, tim.items as tim, roger.items as roger
from
(select names, items from #temp where names = 'John') as john
cross join
(select names, items from #temp where names = 'Tim') as tim
cross join
(select names, items from #temp where names = 'Roger') as roger

It is quite simple with a cross join which is clearly explained in any good
basic SQL book. In your case, you can do:

SELECT t1.number_items AS "john",
t2.number_items AS "tim",
t3.number_items AS "roger"
FROM tbl t1, tbl t2, tbl t3
WHERE t1.name = 'john'
AND t2.name = 'tim'
AND t3.name = 'roger' ;

--
Anith

[quoted text, click to view]

You and I have the exact same execution plan, once I add "distinct" to yours

I have a problem, which I can explain but am struggling to translate into
SQL syntax.

myTable has 3 columns, with some example values
ID NAME NUMBER_ITEMS
1 john 4
2 tim 3
3 john 2
4 tim 1
5 roger 6
6 roger 7

Essentially, I am trying to derive every possible combination between NAME
and NUMBER_ITEMS , but as a set.
The set being "john,tim,roger".which conceptually become the columns
e,g
JOHN TIM ROGER
combination1 4 3 6
combination 2 4 1 6
combination3 4 3 7
combination4 4 1 7


Ultimately, I just need them as a coma delimited string. But , most
importantly I need to be able to get past STEP 1

Thanks
Jack