MIN MAX和所有值以逗号分隔SQL Server

I have the following problem: I have a table with employees and emails.

我有以下问题：我有一张包含员工和电子邮件的表格。

• employee1 received 7 emails
• employee1收到7封电子邮件
• employee2 received 4 emails
• employee2收到4封电子邮件
• .
• 。
• .
• 。

Now I need for the employees the max date, the min date and all receivedates of the emails in one column in a comma-separated format.

现在，我需要员工以逗号分隔格式在一列中显示电子邮件的最大日期，最小日期和所有收到的内容。

I have the following:

我有以下内容：

SELECT ... 
FROM
   (SELECT 
        MAX(Datum) as MDatum, MIN(Datum) as MinDatum,
        Sender, Betreff,
        CASE 
           WHEN MIN(Datum) = MAX(Datum) 
              THEN MIN(Datum) 
              ELSE dbo.explode(datum) <- my problem
        END AS VALUE,
        Nummer
    FROM 
        #NEWTABLE 
    WHERE 
        not nummer IS NULL
        AND Sender <> ''total'' 
    GROUP BY 
        Sender, Betreff, Nummer, datum <- second problem
         ) x 
PIVOT 
    (max(VALUE)
         FOR [Nummer] IN (' + @cols2 + ')
    ) p

@cols2 are the dynamic employees

@cols2是充满活力的员工

When I group by datum, I only get the max(value) in pivot

当我按基准分组时，我只获得枢轴中的最大值（值）

How could I solve that?

我怎么能解决这个问题？

kindest regards, frank

亲切的问候，坦率地说

ADDED:

添加：

here is my table(#NEWTABLE):enter image description here

这是我的桌子（#NEWTABLE）：在这里输入图像描述

Nummer |    Betreff |             Datum |
----   | ----       | ----              |
1       2.000 EUR Rente   06.03.2017
1       2.000 EUR Rente   NULL
1       2.000 Rente       04.03.2017
1       2.000 Rente       12.02.2017
1       2.000 Rente       12.03.2017
1       2.000 Rente       19.02.2017
1       2.000 Rente       19.03.2017
1       2.000 Rente       27.02.2017
1       2.000 Rente       NULL
1       2.000 Rente       27.02.2017
1       2.000 Rente       NULL
2       2.000 EUR Rente   06.03.2017
2       2.000 EUR Rente   NULL
2       2.000 Rente       04.03.2017
2       2.000 Rente       12.02.2017
2       2.000 Rente       12.03.2017
2       2.000 Rente       19.02.2017
2       2.000 Rente       19.03.2017
2       2.000 Rente       27.02.2017
2       2.000 Rente       NULL
2       2.000 Rente       27.02.2017
2       2.000 Rente       NULL

so you have nummer, that is employe, betreff and datum, the received date

所以你有nummer，即employee，betreff和datum，收到的日期

expected ouput should be:enter image description here

预期的输出应该是：在此处输入图像描述

nummer  mindate     maxdate     alldates
1       12.02.2017  19.03.2017  12.02.2017,
                                19.02.2017,
                                27.02.2017,
                                27.02.2017,
                                04.03.2017,
                                06.03.2017,
                                12.03.2017,
                                19.03.2017
2       12.02.2017  19.03.2017  12.02.2017,
                                19.02.2017,
                                27.02.2017,
                                27.02.2017,
                                04.03.2017,
                                06.03.2017,
                                12.03.2017,
                                19.03.2017

kindest regards, frank

亲切的问候，坦率地说

4 个解决方案

#1

Hope this helps

希望这可以帮助

;WITH cte_Table (Nummer,Betreff,Datum) AS
(
SELECT 1,'2.000 EUR Rente',CAST('2017-03-07' AS DATE) UNION ALL
SELECT 1,'2.000 EUR Rente',NULL UNION ALL
SELECT 1,'2.000 Rente','2017-03-04' UNION ALL
SELECT 1,'2.000 Rente','2017-02-12' UNION ALL
SELECT 1,'2.000 Rente','2017-03-12' UNION ALL
SELECT 1,'2.000 Rente','2017-02-19' UNION ALL
SELECT 1,'2.000 Rente','2017-03-19' UNION ALL
SELECT 1,'2.000 Rente','2017-02-27' UNION ALL
SELECT 1,'2.000 Rente',NULL UNION ALL
SELECT 1,'2.000 Rente','2017-02-27' UNION ALL
SELECT 1,'2.000 Rente',NULL UNION ALL
SELECT 2,'2.000 EUR Rente','2017-03-06' UNION ALL
SELECT 2,'2.000 EUR Rente',NULL UNION ALL
SELECT 2,'2.000 Rente','2017-03-04' UNION ALL
SELECT 2,'2.000 Rente','2017-02-12' UNION ALL
SELECT 2,'2.000 Rente','2017-03-12' UNION ALL
SELECT 2,'2.000 Rente','2017-02-19' UNION ALL
SELECT 2,'2.000 Rente','2017-03-19' UNION ALL
SELECT 2,'2.000 Rente','2017-02-27' UNION ALL
SELECT 2,'2.000 Rente',NULL UNION ALL
SELECT 2,'2.000 Rente','2017-02-27' UNION ALL
SELECT 2,'2.000 Rente',NULL
)
,cte_MinMax(nummer,mindate,maxdate) AS
(
SELECT Nummer,MIN(Datum),MAX(Datum)
FROM cte_Table
GROUP BY Nummer
)
,cte_CSV AS
(
SELECT DISTINCT a.Nummer, STUFF((    SELECT ', ' + CONVERT(VARCHAR(10),b.Datum,104) AS [text()]
                        FROM cte_Table b
                        WHERE a.Nummer = b.Nummer
                        FOR XML PATH('')), 1, 1, '' ) AS alldates
FROM cte_Table a
)
SELECT b.nummer,b.mindate,b.maxdate,a.alldates 
FROM cte_CSV a
JOIN cte_MinMax b
ON a.Nummer = b.nummer

#2

Here is a solution with FOR XML PATH(''):

这是FOR XML PATH（''）的解决方案：

SELECT Nummer,min(Datum) miDat, max(Datum) maDat,
       STUFF( (SELECT ','+char(10) + CONVERT(char(10),Datum,104) FROM #tmp 
               WHERE NOT Datum is null AND Nummer=o.Nummer
               ORDER BY Datum
               FOR XML PATH('')), 1, 2, '' ) as dates 
FROM #tmp o GROUP BY Nummer

The STUFF() function simply takes away the first two characters.

STUFF（）函数只删除前两个字符。

A working demo can be found here: http://rextester.com/PUG64308

可以在此处找到工作演示：http：//rextester.com/PUG64308

(I changed the sample data a little bit in order to get slightly different results for the two numbers.)

（我稍微改变了样本数据，以便得到两个数字略有不同的结果。）

Edit: your changed result can be easily achieved by modifying the query in the following way:

编辑：通过以下方式修改查询，可以轻松实现更改后的结果：

SELECT min(Datum) miDat, max(Datum) maDat,
       STUFF( (SELECT ','+char(10) + CONVERT(char(10),Datum,104) FROM #tmp 
               WHERE NOT Datum is null AND Nummer=1
               ORDER BY Datum
               FOR XML PATH('')), 1, 2, '' ) as dates1,
       STUFF( (SELECT ','+char(10) + CONVERT(char(10),Datum,104) FROM #tmp 
               WHERE NOT Datum is null AND Nummer=2
               ORDER BY Datum
               FOR XML PATH('')), 1, 2, '' ) as dates2
FROM #tmp

although in my opinion the columns "Sender" and Betreff" do not make sense anymore in such a result scenario.

虽然在我看来，“Sender”和Betreff这两个专栏“在这样的结果场景中不再有意义。

A working example can be found here: http://rextester.com/EATE35782

可以在此处找到一个工作示例：http：//rextester.com/EATE35782

#3

it works like a charme... but i made a mistake, sorry, the expected output should look like this(with pivot) enter image description here

它的作用就像一个魅力......但是我犯了一个错误，对不起，预期的输出应该看起来像这样（带有支点）在这里输入图像描述

sender | betreff | mindate      | maxdate     | 1             | 2
blubb  | blah    | 12.02.2017   | 19.03.2017  | 12.02.2017,   | 12.02.2017,
                                                19.02.2017,     19.02.2017,
                                                27.02.2017,     27.02.2017,
                                                27.02.2017,     27.02.2017,
                                                04.03.2017,     04.03.2017,
                                                06.03.2017,     06.03.2017,
                                                12.03.2017,     12.03.2017,
                                                19.03.2017      19.03.2017

kindest regards, frank

亲切的问候，坦率地说

#4

i got it:

我知道了：

SELECT ... 
FROM
   (SELECT sender,Nummer,min(Datum) MinDatum, max(Datum) MDatum,betreff,
                    STUFF( (SELECT '',''+char(10) + CONVERT(char(10),Datum,104) FROM #NEWTABLE 
                    WHERE NOT Datum is null AND Nummer=o.Nummer and sender = o.sender and betreff = o.betreff
                    group by Datum,sender,Nummer,betreff
                    ORDER BY Datum
                    FOR XML PATH('''')), 1, 2, '''' ) as VALUE 
                    FROM #NEWTABLE o where sender <> ''total'' GROUP BY sender,Nummer,betreff
         ) x 
PIVOT 
    (max(VALUE)
         FOR [Nummer] IN (' + @cols2 + ')
    ) p