CODEWITHSUNDEEP
sqlsql-serverpivot
Hans Jørgensen
Hans Jørgensen

Reputation: 33

Counting entries and pivot tables in SQL Server

I have a question regarding pivot tables and summarizing

The source select statement output the following structure

Customer|ProductID
A       |1
A       |4
B       |3
C       |2
D       |3
D       |3
D       |4

I will like to convert this into a table with this structure:

Customer|Count|1|2|3|4
A       |2    |1|0|0|1
B       |1    |0|0|1|0
C       |1    |0|1|0|0
D       |3    |1|0|1|1

"Count" should be the number of products that the customer has bought, but I can't get it to work :(

Here is the SQL:

SELECT customerId, CustomerNo, [1], [2], [3], [4] FROM

(SELECT id AS customerId, 
CustomerNo,
CASE ProductType
WHEN 1 THEN 1 /* Games */
WHEN 2 THEN 2 /* Cards */
WHEN 4 THEN 3 /* Gadgets */
WHEN 5 THEN 3 /* Accessories */
WHEN 6 THEN 4 /* Smartsphone acc. */
ELSE 255
END AS Products,
FROM
ProductSales
) AS SourceTable 

PIVOT ( 
COUNT(Products)
FOR Products IN ([1], [2], [3], [4])
) AS PivotTable

This works fine, but I need to add some kind of COUNT in the first SELECT-statement in order to Count the number of records that each customer has.

Any help will be greatly appreciated. Thanks in advance :)

/Hans

Upvotes: 3

Views: 1309

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

No problems doing it with PIVOT, based on your current query.
Just add the following column to the SELECT.

[1]+[2]+[3]+[4] as [Count]

Demo

create table t (customer char(1),ProductID int)
insert into t values ('A',1),('A',4),('B',3),('C',2),('D',3),('D',3),('D',4)

select      customer
           ,[1]+[2]+[3]+[4] as [Count]
           ,[1],[2],[3],[4]

from        t pivot (count(ProductID) for ProductID in ([1],[2],[3],[4])) t

+----------+-------+---+---+---+---+
| customer | Count | 1 | 2 | 3 | 4 |
+----------+-------+---+---+---+---+
| A        | 2     | 1 | 0 | 0 | 1 |
+----------+-------+---+---+---+---+
| B        | 1     | 0 | 0 | 1 | 0 |
+----------+-------+---+---+---+---+
| C        | 1     | 0 | 1 | 0 | 0 |
+----------+-------+---+---+---+---+
| D        | 3     | 0 | 0 | 2 | 1 |
+----------+-------+---+---+---+---+

Upvotes: 1

sagi
sagi

Reputation: 40481

Try with conditional aggregation instead of Pivot:

SELECT t.customerid,
       COUNT(*) as cnt,
       MAX(CASE WHEN t.productid = 1 THEN 1 ELSE 0 END) as first,
       MAX(CASE WHEN t.productid = 2 THEN 1 ELSE 0 END) as second,
       MAX(CASE WHEN t.productid = 3 THEN 1 ELSE 0 END) as third
FROM YourTable t
GROUP BY t.customerid

Upvotes: 1

Related Questions