Here is my data set in raw format. 


  Region   |  Name  |  Code
North      |  John  |  A
North      |  John  |  A
North      |  John  |  B
North      |  Tom   |  A
North      |  Tom   |  C
West       |  John  |  B
West       |  John  |  A
West       |  John  |  B
West       |  Tom   |  A


I need to figure out how to get this result using SQL. 


  Region   |  Name  |  A  |  B  |  C  
 North     |  John  |  2  |  1  |  0
 North     |  Tom   |  1  |  0  |  1
 West      |  John  |  1  |  2  |  0
 West      |  Tom   |  1  |  0  |  0 


How can I accomplish this using SQL?
 

SELECT region, nam, COALESCE(a,0) AS A, COALESCE(b,0) AS B, COALESCE(c,0) AS C FROM 
(
	SELECT region, name, code, COUNT(*) AS N FROM YourTable GROUP BY region, name, code
)a
PIVOT ( MAX(n) FOR code IN ([a],[b],[c]))P

quote:

---

Originally posted by James K

If you know the codes that you are going to have in advance, you can use the PIVOT operator like shown below. If not, you will need to use dynamic PIVOT (google for Madhivanan's dynamic pivot code)

SELECT region, nam, COALESCE(a,0) AS A, COALESCE(b,0) AS B, COALESCE(c,0) AS C FROM 
(
	SELECT region, name, code, COUNT(*) AS N FROM YourTable GROUP BY region, name, code
)a
PIVOT ( MAX(n) FOR code IN ([a],[b],[c]))P

---

quote:

---

Originally posted by ugh3012

quote:

---

Originally posted by James K

If you know the codes that you are going to have in advance, you can use the PIVOT operator like shown below. If not, you will need to use dynamic PIVOT (google for Madhivanan's dynamic pivot code)

SELECT region, nam, COALESCE(a,0) AS A, COALESCE(b,0) AS B, COALESCE(c,0) AS C FROM 
(
	SELECT region, name, code, COUNT(*) AS N FROM YourTable GROUP BY region, name, code
)a
PIVOT ( MAX(n) FOR code IN ([a],[b],[c]))P

---

I m getting this message 'No column name was specified for column 2 of 'a'.'

I am at lost about this issue. What I am doing wrong? When I double click the error, it highlights the row with ')a' right above the word pivot from your example.

---

SELECT region, nam, COALESCE(a,0) AS A, COALESCE(b,0) AS B, COALESCE(c,0) AS C FROM 
(
	SELECT region, <your expression AS columnname, code, COUNT(*) AS N FROM YourTable GROUP BY region, name, code
)a
PIVOT ( MAX(n) FOR code IN ([a],[b],[c]))P