计算动态项目

Question

我有一个动态数据，例如：

Name Date Class
______________
John 15/06/2019 AB
John 14/06/2019 B
John 13/06/2019 C
Tom  14/06/2019 C
Tom  13/06/2019 D

我想要结果：

Name ClassA ClassB ClassC ClassD ClassCount
_____________________
John 1 2 1 0 4
Tom  0 0 1 1 2

我尝试了一些方法，但它不起作用。谢谢。

Answer 1

您需要执行一些动态SQL才能获得所需的结果。检查以下脚本。

注意：考虑类名 A 到 Z

查看此演示以获取更多详细信息。

DECLARE @ColumnList VARCHAR(MAX)
DECLARE @sqlCommand VARCHAR(MAX)
;WITH CTE(Class,Name,Date)
AS
(
    SELECT A.Cl Class,B.Name,B.Date
    FROM (
        SELECT 'A' Cl UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL   SELECT 'D' UNION ALL SELECT 'E' UNION ALL
        SELECT 'F' UNION ALL SELECT 'G' UNION ALL   SELECT 'H' UNION ALL    SELECT 'I' UNION ALL    SELECT 'J' UNION ALL
        SELECT 'K' UNION ALL    SELECT 'L' UNION ALL    SELECT 'M' UNION ALL    SELECT 'N' UNION ALL    SELECT 'O' UNION ALL
        SELECT 'P' UNION ALL    SELECT 'Q' UNION ALL    SELECT 'R' UNION ALL    SELECT 'S' UNION ALL    SELECT 'T' UNION ALL
        SELECT 'U' UNION ALL    SELECT 'V' UNION ALL    SELECT 'W' UNION ALL    SELECT 'X' UNION ALL    SELECT 'Y' UNION ALL    SELECT 'Z' 
    )A
    INNER JOIN <your_table> B
    ON  B.Class LIKE '%' + A.Cl + '%'
)
SELECT  @ColumnList =
STUFF((SELECT DISTINCT  ('],[' +  A.Class)
        FROM CTE A
        --ORDER BY (A.Class) 
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')+']'
FROM CTE

SET @sqlCommand= 
N'
SELECT 
Name,A ClassA, B ClassB,C ClassC,D ClassD,
A+B+C+D ClassCount
FROM
(
    SELECT Name,'+SUBSTRING(@ColumnList,2,LEN(@ColumnList))+'
    FROM
    (
        SELECT A.Cl Class,B.Name,B.Date
        FROM (
            SELECT ''A'' Cl UNION ALL SELECT ''B'' UNION ALL SELECT ''C'' UNION ALL     SELECT ''D'' UNION ALL SELECT ''E'' UNION ALL
            SELECT ''F'' UNION ALL SELECT ''G'' UNION ALL   SELECT ''H'' UNION ALL  SELECT ''I'' UNION ALL  SELECT ''J'' UNION ALL
            SELECT ''K'' UNION ALL  SELECT ''L'' UNION ALL  SELECT ''M'' UNION ALL  SELECT ''N'' UNION ALL  SELECT ''O'' UNION ALL
            SELECT ''P'' UNION ALL  SELECT ''Q'' UNION ALL  SELECT ''R'' UNION ALL  SELECT ''S'' UNION ALL  SELECT ''T'' UNION ALL
            SELECT ''U'' UNION ALL  SELECT ''V'' UNION ALL  SELECT ''W'' UNION ALL  SELECT ''X'' UNION ALL  SELECT ''Y'' UNION ALL  SELECT ''Z'' 
        )A
        INNER JOIN <your_table> B
        ON  B.Class LIKE ''%'' + A.Cl + ''%''
    )A
    PIVOT(
        COUNT(Date)
        FOR Class IN('+SUBSTRING(@ColumnList,2,LEN(@ColumnList))+')
    )PVT    
)AA'

--PRINT @sqlCommand
EXEC (@sqlCommand)