CODEWITHSUNDEEP
sql-servert-sql
Big Pimpin
Big Pimpin

Reputation: 437

Show Sale Data By Salesman By Region

I need a SQL Server query to show a grid of sales by region by sales person like this:

region   Mitchell   Mark    Manny     Moe
-----------------------------------------
West        0        0        0        0
North       0        1        1        1
East        1        0        0        0
South       0        0        0        0

How can I have my SQL query output the data like this? I know I could use this to return the data but it is not in the correct breakdown or format.

Select region, salesperson, Count(Sales)
from salesdatabase
group by region, salesperson

Upvotes: 1

Views: 255

Answers (1)

dario
dario

Reputation: 5269

You can PIVOT your table. Try this:

;WITH DATA AS
(
    SELECT region, salesperson, Sales
    FROM salesdatabase
)
SELECT region
      ,[Mitchell]
      ,[Mark]
      ,[Manny]
      ,[Moe]
FROM DATA AS D
PIVOT (COUNT(Sales) FOR salesperson IN ([Mitchell], [Mark], [Manny], [Moe])) AS P;

You can find more information here.

Upvotes: 2

Related Questions