Как использовать разные и считать для нескольких столбцов в запросе SQL

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)

Согласно моему запросу мне не удалось получить как ниже. Как я могу получить результат, как показано ниже:

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

Answer 1

Пожалуйста, попробуйте это:

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

Answer 2

Я бы написал запрос используя STUFF() скорее, чем SUBSTRING() в конце. В любом случае, я думаю, что вам просто нужно SELECT DISTINCT в подзапросе:

SELECT a.Cust_Name, a.Active_count, Contract = b.List
FROM (SELECT Cust_Name, COUNT(DISTINCT Contract) AS Active_count
      FROM  mytable
      WHERE STS_CODE = 'Active'
      GROUP BY Cust_Name
     ) a CROSS  APPLY
     (SELECT STUFF((SELECT DISTINCT b.Contract + ',  '
                    FROM Mytable b
                    WHERE b.Cust_Name = a.Cust_Name AND  b.Status = 'Active'
                    FOR XML PATH(''), TYPE
                   ).value('.',  'nvarchar(MAX)'), 1, 2, ''
                  )
     ) AS b(List);