Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Von DataSet zu Coma Separated List und dann wieder zurück

18. Dezember 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Es ist eine häufige Anforderung, Werte von mehreren Datensätze aus einem MS Sql Server per T-SQL als eine Coma Separated List (csv) auszugeben, z.B. im Reporting.

Analog dazu gibt es die Anforderung zum Umkehrschluß: Man hat einen csv-String und möchte das als RecordSet erhalt, um z.B. als Referenz darauf zu JOINen.

Wie geht’s? Mit ein paar kleinen Tricks recht einfach.

Zunächst DataSet => csv

Hier gibt es wie immer mehrere Varianten, ich zeige 2. Die eine kumuliert die Wert über eine Variable und gibt die dann aus, die andere verwendet die XML Funktion (ab Version 2005) und ersetzt für das Ergebnis die Tags.

 

 SET NOCOUNT ON; 
 -- Werte anlegen 
 CREATE TABLE #csv 
  (value varchar(3)); 
 INSERT INTO #csv VALUES ('Jan'); 
 INSERT INTO #csv VALUES ('Feb'); 
 INSERT INTO #csv VALUES ('Mar'); 
 INSERT INTO #csv VALUES ('Apr'); 
 INSERT INTO #csv VALUES ('May'); 
 INSERT INTO #csv VALUES ('Jun'); 
 INSERT INTO #csv VALUES ('Jul'); 
 INSERT INTO #csv VALUES ('Aug'); 
 INSERT INTO #csv VALUES ('Sep'); 
 INSERT INTO #csv VALUES ('Oct'); 
 INSERT INTO #csv VALUES ('Nov'); 
 INSERT INTO #csv VALUES ('Dec'); 
 GO 
   
 -- Weg über eine Variable 
 DECLARE @csv varchar(200) = ''; 
 SELECT @csv = @csv + value + ';' 
 FROM #csv 
 -- Liste ausgeben 
 SELECT @csv AS csv; 
   
 -- Über XML; da muss zwischendurch konvertiert und Tags entfernt werden 
 DECLARE @sep char(1); 
 SET @sep = ';'; 
 SELECT REPLACE(REPLACE(StrTbl.StrValue, '<tag>', ''), '</tag>', '') AS csv 
 FROM  
  (SELECT StrValue = 
  CONVERT(varchar(400), 
  (SELECT tag = Value + @sep 
  FROM #csv 
  FOR XML PATH ('tag'), Type 
  ) 
  ) 
  ) AS StrTbl 
   
 GO -- Aufräumen 
 DROP TABLE #csv; 

 

Ergebnis:

 

 csv 
 ----------------------------------------------- 
 Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec; 
   
 csv 
 ----------------------------------------------- 
 Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec; 

 

 

So, und nun wieder zurück. Es gibt einen csv String und der soll nun wieder aufgesplittet. Auch nicht wirklich aufwendig, wenn man es über eine CTE (Common Table Expression) erledigt:

 

 DECLARE @csv varchar(200); 
 DECLARE @sep char(1); 
   
 -- Voraussetzung: Abschließender Separator 
 SET @csv = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec;'; 
 SET @sep = ';'; 
   
 WITH cte (Id, Value, Rest) 
 AS (SELECT 1 as Id 
  ,SUBSTRING(@csv, 1, CHARINDEX(@sep, @csv, 1) - 1) AS Value 
  ,SUBSTRING(@csv, CHARINDEX(@sep, @csv, 1) + 1, LEN(@csv)) AS Value 
  UNION ALL 
  SELECT Id + 1 AS Id 
  ,SUBSTRING(Rest, 1, CHARINDEX(@sep, Rest, 1) - 1) AS Value 
  ,SUBSTRING(Rest, CHARINDEX(@sep, Rest, 1) + 1, LEN(Rest)) AS Value 
  FROM cte 
  WHERE CHARINDEX(@sep, Rest, 1) <> 0) 
 SELECT Id, Value 
 FROM cte;  

 

Ergebnis:

 

 Id Value 
 ----------- ------ 
 1 Jan 
 2 Feb 
 3  Mar 
 4 Apr 
 5 May 
 6 Jun 
 7 Jul 
 8 Aug 
 9 Sep 
 10 Oct 
 11 Nov 
 12 Dec 

Diesen Post teilen

Repost 0
Um über die neuesten Artikel informiert zu werden, abonnieren:

Kommentiere diesen Post

Ebis 01/08/2010 12:38


Hi Olaf, ohne REPLACE:

DECLARE @sep char(1);
SET @sep = ';';
SELECT CAST ( (SELECT (SELECT Value + @sep
FROM #csv
FOR XML PATH (''), Type
) AS [Text()]
) AS NVARCHAR(MAX)
)