MIN MAX and all values comma separated SQL Server

Question

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

• employee1 received 7 emails
• employee2 received 4 emails
• .
• .

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

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

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

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

Answer 1

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

Answer 2

Here is a solution with 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.

A working demo can be found here: 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.

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

Answer 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

Answer 4

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