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...

15.jpg
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