concatenate dates for two distinct cases, Sql Server

concatenate dates for two distinct cases

CREATE table #tmp (Nummer int, Betreff varchar(32) null, Datum date null );

INSERT INTO #tmp
          SELECT 1,'2.000 EUR Rente','2017-03-06'
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-02'
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-29'
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;

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

drop table #tmp


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development