How to use distinct and count for multiple columns in SQL query

Question

MyTable:

+-----------+-----------+---------+
| Cust_Name | Contract  | Status  |
+-----------+-----------+---------+
| ABC       | CISCO     | Active  |
| DEF       | Microsoft | Expired |
| ABC       | HP        | Expired |
| DEF       | DELL      | Active  |
| ABC       | CISCO     | Active  |
+-----------+-----------+---------+

MS SQL Query :

SELECT Cust_Name, Active_count,
       Contract = substring(List, 1, len(List) - 1)
FROM   (SELECT Cust_Name, COUNT(*) AS Active_count
        FROM  mytable
        WHERE status = 'Active'
        GROUP BY Cust_Name) AS a
CROSS  APPLY (SELECT    (SELECT Contract + ',  '
                 FROM   Mytable b
                 WHERE  b.Cust_Name = a.Cust_Name
                   AND  b.Status = 'Active'
                 FOR XML PATH(''), TYPE).value('.',  'nvarchar(MAX)')) AS b(List)

As per my query i am failed to get like below.How can i get the result like below:

+-----------+--------------+--------------+
| Cust_Name | Active_count |  Contracts   |
+-----------+--------------+--------------+
| ABC       |            2 | CISCO,Oracle |
| DEF       |            1 | DELL         |
+-----------+--------------+--------------+

Answer 1

Please try this:

WITH CTE_Testtable AS (
 SELECT 
  Cust_Name,
  Contract,
  Status 
 FROM
  TESTTable
 WHERE
 Status = 'Active'
 GROUP BY 
 Cust_Name, Contract, Status
)
SELECT
 Cust_Name,
 COUNT(Cust_Name) Active_Count,
 STUFF((SELECT ', ' + Contract
  FROM 
   TESTTable B
  WHERE 
   B.Cust_Name = A.Cust_Name 
   AND Status = 'Active'
  GROUP BY Contract
  FOR XML PATH('')), 1, 2, '') 
FROM
 CTE_Testtable A
WHERE
 Status = 'Active'
GROUP BY 
 Cust_Name