CODEWITHSUNDEEP
sqlsql-serverdatabaseexcel
RalphBiggerton
RalphBiggerton

Reputation: 199

SQL - Grouping under Single Customer ID

I have the following table below, which checks how many orders a customer has which will be used as a look up table / filter for my PowerPivot Model

| CustomerID  |  OrderCntOne   | OrderCntTwo  | OrderCntThree |
---------------------------------------------------------------
| 0           |   X            |              |               |
| 0           |                | X            |               |
| 0           |                |              |  X            |
| 1           |   X            |              |               |
| 2           |   X            |              |               |
| 3           |   X            |              |               |
| 3           |                | X            |               |
| 4           |   X            |              |               |
| 4           |                | X            |               |
| 4           |                |              |  X            |
---------------------------------------------------------------

which was created with this SQL query, looking at the number of the given order for the customer (order count) and putting an x depending on if that customer has 1, 2, or 3 orders in their life to date ignoring anything more than 3 orders. 

SELECT [CustomerID],
   CASE   
      WHEN OrderCount = 1 THEN 'X'  
   END AS OrdCntOne,
Case
  WHEN OrderCount = 2 THEN 'X'
   End as OrdCntTwo,
Case
   WHEN OrderCount = 3 THEN 'X'
End as OrdCntThree, 
FROM [dbo].[Table]
group by CustomerID, ordercount

However, I want the table to look like this so it's only one line per customer ID

| CustomerID  |  OrderCntOne   | OrderCntTwo  | OrderCntThree |
---------------------------------------------------------------
| 0           |   X            | X            |  X            |
| 1           |   X            |              |               |
| 2           |   X            |              |               |
| 3           |   X            | X            |               |
| 4           |   X            | X            |  X            |
---------------------------------------------------------------

but am not sure how to just show 1 customer Id per line with the right "x's" next to that single ID. Can't do just a group by customer id because of the order count which is causing the multiple customer Ids.

Any thoughts would be helpful.

Upvotes: 0

Views: 870

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Just remove the ordercount from group by and use MAX over case expressions.

select [CustomerID],
    max(case when OrderCount = 1 then 'X' end) as OrdCntOne,
    max(case when OrderCount = 2 then 'X' end) as OrdCntTwo,
    max(case when OrderCount = 3 then 'X' end) as OrdCntThree
from [dbo].[Table]
group by CustomerID;

Upvotes: 3

Related Questions