2016年7月20日 星期三

SQL Server: combining multiple rows into one row


I have a SQL query like this;

SELECT *   FROM Jira.customfieldvalue  WHERE CUSTOMFIELD = 12534  AND ISSUE = 19602

And that's the results;

enter image description here

What I want is; showing in one row (cell) combined all STRINGVALUE's and they are separated with a comma. Like this;

SELECT --some process with STRINGVALUE--  FROM Jira.customfieldvalue  WHERE CUSTOMFIELD = 12534  AND ISSUE = 19602    Araç Listesi (C2, K1 vb.Belgeler; yoksa Ruhsat Fotokopileri), Min. 5  araç plakası için İnternet Sorgusu, Son 3 Yıla Ait Onaylı Yıl Sonu  Bilanço + Gelir Tablosu, Son Yıl (Yıl Sonuna ait) Detay Mizanı, İçinde  Bulunduğumuz Yıla ait Ara Dönem Geçici Vergi Beyannamesi, Bayi Yorum  E-Maili, Proforma Fatura


You can achieve this is to combine For XML Path and STUFF as follows:

SELECT (STUFF((          SELECT ', ' + StringValue          FROM Jira.customfieldvalue          WHERE CUSTOMFIELD = 12534          AND ISSUE = 19602          FOR XML PATH('')          ), 1, 2, '')      ) AS StringValue
 

沒有留言:

張貼留言