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

Repost0

"Visual Basic 2008 - Das Entwicklerbuch" von Microsoft Press als kostenloser Download

Dokumentations-Sets zum Thema MS Sql Server

Zurück zur Home

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) )

Antworten

2016
- April 1
- März 2
2013
- August 1
- Juni 2
2012
- November 1
- Juni 1
- April 2
- März 2
- Januar 1
2011
- Dezember 1
- November 2
- Oktober 1
- August 4
- Juli 1
- Mai 1
- April 5
- März 4
- Februar 5
2010
- Mai 1
- April 11
- März 5
- Februar 2
- Januar 14
2009
- Dezember 3
- November 10
- Oktober 11
- September 9
- August 15
- Juli 11
- Juni 6
- Mai 8
- April 9
- März 12
- Februar 19
- Januar 26
2008
- Dezember 27