CODEWITHSUNDEEP
sqlsql-server
Arif H-Shigri
Arif H-Shigri

Reputation: 157

SQL: query to categorize items

I have table product

area  productname   count
a1     p1            5
a2     p2            6
a1     p2            9
a2     p3            8
a1     p3            1

I want it to be like this; if it is possible in SQL, I'm using SQL Server 

a1     count(p1)    count (p2)  count(p3)
a2     count(p1)    count (p2)  count(p3)
.
.
an     count(p1)    count (p2)  count(p3)

Upvotes: 1

Views: 165

Answers (1)

zedfoxus
zedfoxus

Reputation: 37039

For the specific problem you are trying to solve, the following query will work. Evaldas suggested the link that also provides a great example for dynamic pivoting.

select area, p1, p2, p3
from
(select area, productname, counter from test) t
pivot 
(
  min(counter)
  for productname in (p1, p2, p3)
) as piv
order by area;

Example: http://sqlfiddle.com/#!3/2ef59/19

Upvotes: 2

Related Questions