Need help with PIVOT query. All outputs become zeros

Table look like this:
[c1] [varchar](10) NULL
[c2] [varchar](10) NULL
[c3] [int] NULL
--------------------------------
Contains this:
c1 c2 c3
KATE MILK 5
KATE BEER 5
KATE MILK 5
KATE BEER 5
HANNAH MILK 6
HANNAH BEER 6
HANNAH MILK 6
HANNAH BEER 6
HANNAH MILK 6
--------------------------------
The query:
SELECT C1,
[R1],
[R2],
[R3]
FROM
( SELECT C1,C2,C3
FROM __lab) A
PIVOT ( COUNT(C2)
FOR C2 IN ( [R1],[R2],[R3] )
) B
--------------------------------
Gives this:
C1 R1 R2 R3
KATE 0 0 0
KATE 0 0 0
HANNAH 0 0 0
KATE 0 0 0
HANNAH 0 0 0
KATE 0 0 0
--------------------------------
But what im trying to get is this: (example numbers only)
HANNAH KATE
BEER 30 20
MILK 20 30

I want the output to show how many times HANNAH and KATE have BEER vs MILK in all rows. Just ignore c3
How can i get the output above with PIVOT function?

Thanks in advance

//Mr

You'll need to flip over the pivot
so you've got...

Lets change it to...

So C2 (Drink) is now your first column, pivot columns are now populated from C1 (name).

thanks for your replay, unfournately it dosen't work.
It seems like the C2 is an invalid column in "SELECT C2, [MILK], [BEER]"

If i change the "PIVOT ( COUNT(c2)" to "PIVOT ( COUNT(c3)" it works but still give me zeros

Try

sorry, can't SUM varchar columns.

"Operand data type varchar is invalid for sum operator."

sum(c3) then

yes that works but that's not the result i want.
I want the query to count how many times "milk" and "beer" there is. Not the sum of the c3

thanks for your effort

You've asked for
HANNAH KATE
BEER 30 20
MILK 20 30


What does my query return?

If you do a count(c3) I assume you would get

C1 HANNAH KATE
MILK 3 2
BEER 3 2

Wouldn't sum(c3) give you the result you are after?

since C3 contains more then value of 1 (5 for Kate and 6 for Hannah) the sum would give me the summarize of the value for that column and not a count for each "MILK" and "BEER"

table look like this:

C1, C2, C3
KATE, MILK, 5
KATE, BEER, 5
HANNAH, MILK, 6