MSSQL-cannot find a title...sorry

[kava]

hello, I'm kinda newbie in databases.

I have a table like that:

DB is MSSQL 2003

Name is varchar

Fruit is int (i used the name just to avoid mess with numbers)
the number and the type of the fruit is fixed (in real case are 1,2,3 and 4)

Qty is int

+++++++++++++++++++++
+ Name + Fruit + Qty +
+++++++++++++++++++++
+++++++++++++++++++++
+ Paul + Apple + 3 +
+ Paul + Banana + 2 +
+ Mark + Apple + 17 +
+ Paul + Pine + 1 +
+ Mark + Pine + 3 +
+++++++++++++++++++++

Id like to have this output but I'm a bit underprepared to do that...

+++++++++++++++++++++++++
Name + Apple + Banana + Pine +
+++++++++++++++++++++++++
Paul + 3 + 2 + 1 +
Mark + 17 + 0 + 3 +
+++++++++++++++++++++++++

[cutepinkbunnies]

This should do the trick :) Although inefficient, I used self-left outer joins and the isnull function to give us a 0 value for quantity.

+++++++++++++++++++++++++
Name + Apple + Banana + Pine +
+++++++++++++++++++++++++
Paul + 3 + 2 + 1 +
Mark + 17 + 0 + 3 +
+++++++++++++++++++++++++

select 
	t.[name] as 'Name', 
	isnull(sum(a.qty),0) as 'Apple', 
	isnull(sum(b.qty),0) as 'Bananna', 
	isnull(sum(c.qty),0) as 'Pine'
from 
	table_1 t
	LEFT JOIN table_1 a on 
		a.name = t.name 
		and a.fruit = 1
		and t.fruit = 1
	LEFT JOIN table_1 b on 
		b.name = t.name 
		and b.fruit = 2
		and t.fruit = 2
	LEFT JOIN table_1 c on 
		c.name = t.name 
		and c.fruit = 3
		and t.fruit = 3
group by t.[name]
order by t.[name] desc

Here is the resultset I get with the same data you have. Remember I used Table_1 as a table I could quickly throw data into, you will have to change the tablename...not the alias.

Paul	3	2	1
Mark	17	0	3

Also, hope you enjoy the explicit joins...I can't stand a cluttered SQL statement that has a thousand different conditions in the where clause!

Let us know how this works out for you!