Pivoting in SQL Server

Here is a QUERY asked by one of my blog reader. Thought of resolving this issue for him.

respected sir,

tbl1 ID primary key

tbl2 FK_prodct foreign key

How do create for that query.i am very confused that ,plz help ..give me a idea or query...

Here is the sample query to retrieve the output,

CREATE TABLE PRODUCTS(ID INT , NAME VARCHAR(100))
INSERT INTO PRODUCTS VALUES (1,'CAD')

INSERT INTO PRODUCTS VALUES (2,'PTC')

CREATE TABLE USERS(ID INT, PRODUCT VARCHAR(100))

INSERT INTO USERS VALUES (1,'ASIA')

INSERT INTO USERS VALUES (1,'ASIA')

INSERT INTO USERS VALUES (2,'ASIA')

INSERT INTO USERS VALUES (2,'EUROPE')

select * from users

select * from

(

select * from

(
select p.id, name, PRODUCT
from dbo.PRODUCTS p inner join

USERS SOD

on sod.id = p.id

)p

pivot

(count(name) for PRODUCT in ([ASIA],[EUROPE]

))

as pvt1

)tbl1

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech