SQL Server combining multiple rows into one row

Question

I have a SQL query like this   SELECT    FROM Jira customfieldvalue WHERE CUSTOMFIELD   12534 AND ISSUE   19602   And that s the results     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   plakasi i  in Internet Sorgusu  Son 3 Yila Ait Onayli Yil Sonu Bilan  o   Gelir Tablosu  Son Yil  Yil Sonuna ait  Detay Mizani  I  inde Bulundugumuz Yila ait Ara D  nem Ge  ici Vergi Beyannamesi  Bayi Yorum E-Maili  Proforma Fatura   How can I do that

User · Answer

x000D   x000D  declare  maxColumnCount int 0   declare  Query varchar max       declare  DynamicColumnName nvarchar MAX       -- table type variable that store all values of column row no  DECLARE  TotalRows TABLE  row count int   INSERT INTO  TotalRows  row count   SELECT  ROW NUMBER   OVER PARTITION BY InvoiceNo order by InvoiceNo Desc   as row no FROM tblExportPartProforma  -- Get the MAX value from  TotalRows table  set  maxColumnCount   select max row count  from  TotalRows    -- loop to create Dynamic max case and store it into local variable   DECLARE  cnt INT   1   WHILE  cnt  lt    maxColumnCount  BEGIN    set  DynamicColumnName   DynamicColumnName      Max case when row no    cast  cnt as varchar    then InvoiceType end  as InvoiceType  cast  cnt as varchar           set  DynamicColumnName   DynamicColumnName      Max case when row no    cast  cnt as varchar    then BankRefno end  as BankRefno  cast  cnt as varchar                 set  DynamicColumnName   DynamicColumnName      Max case when row no    cast  cnt as varchar    then AmountReceived end  as AmountReceived  cast  cnt as varchar            set  DynamicColumnName   DynamicColumnName      Max case when row no    cast  cnt as varchar    then AmountReceivedDate end  as AmountReceivedDate  cast  cnt as varchar          SET  cnt    cnt   1  END   -- Create dynamic CTE and store it into local variable  query    set  Query        with CTE tbl as               SELECT InvoiceNo InvoiceType BankRefno AmountReceived AmountReceivedDate         ROW NUMBER   OVER PARTITION BY InvoiceNo order by InvoiceNo Desc  as row no        FROM tblExportPartProforma           select      InvoiceNo         DynamicColumnName        FROM CTE tbl      group By InvoiceNo   -- Execute the Query  execute   Query  x000D   x000D   x000D

User · Answer

There are several methods   If you want just the consolidated string value returned  this is a good quick and easy approach  DECLARE  combinedString VARCHAR MAX  SELECT  combinedString   COALESCE  combinedString               stringvalue FROM jira customfieldValue WHERE customfield   12534     AND ISSUE   19602  SELECT  combinedString as StringValue    Which will return your combined string   You can also try one of the XML methods e g    SELECT DISTINCT Issue  Customfield  StringValues FROM Jira customfieldvalue v1 CROSS APPLY   SELECT StringValues                      FROM jira customfieldvalue v2               WHERE v2 Customfield   v1 Customfield                    AND v2 Issue   v1 issue                ORDER BY ID                    FOR XML PATH        D   StringValues   WHERE customfield   12534     AND ISSUE   19602

User · Answer

Using MySQL inbuilt function group concat   will be a good choice for getting the desired result  The syntax will be -  SELECT group concat STRINGVALUE   FROM Jira customfieldvalue WHERE CUSTOMFIELD   12534 AND ISSUE   19602   Before you execute the above command make sure you increase the size of group concat max len else the the whole output may not fit in that cell   To set the value of group concat max len  execute the below command-  SET group concat max len   50000    You can change the value 50000 accordingly  you increase it to a higher value as required

User · Answer

There s a convenient method for this in MySql called GROUP CONCAT  An equivalent for SQL Server doesn t exist  but you can write your own using the SQLCLR  Luckily someone already did that for you   Your query then turns into this  which btw is a much nicer syntax    SELECT CUSTOMFIELD  ISSUE  dbo GROUP CONCAT STRINGVALUE  FROM Jira customfieldvalue WHERE CUSTOMFIELD   12534 AND ISSUE   19602 GROUP BY CUSTOMFIELD  ISSUE   But please note that this method is good for at the most 100 rows within a group  Beyond that  you ll have major performance problems  SQLCLR aggregates have to serialize any intermediate results and that quickly piles up to quite a lot of work  Keep this in mind   Interestingly the FOR XML doesn t suffer from the same problem but instead uses that horrendous syntax

User · Answer

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

User · Answer

CREATE VIEW   dbo   ret vwSalariedForReport  AS      WITH temp1 AS  SELECT      salaried         operationalUnits Title as OperationalUnitTitle FROM     ret vwSalaried salaried LEFT JOIN     prs operationalUnitFeatures operationalUnitFeatures on salaried  Guid    operationalUnitFeatures  FeatureGuid  LEFT JOIN      prs operationalUnits operationalUnits ON operationalUnits id   operationalUnitFeatures OperationalUnitID          temp2 AS  SELECT     t2        STUFF   SELECT   -     t1 OperationalUnitTitle         FROM             temp1 t1          WHERE t1  ID    t2  ID            For XML PATH       2  2      OperationalUnitTitles from temp1 t2   SELECT       Guid       ID      Title      PersonnelNo      FirstName      LastName      FullName      Active      SSN      DeathDate      SalariedType      OperationalUnitTitles FROM      temp2 GROUP BY       Guid       ID      Title      PersonnelNo      FirstName      LastName      FullName      Active      SSN      DeathDate      SalariedType      OperationalUnitTitles

User · Answer

I believe for databases which support listagg function  you can do   select id  issue  customfield  parentkey  listagg stingvalue       within group  order by id  from jira customfieldvalue where customfield   12534 and issue   19602 group by id  issue  customfield  parentkey

[sql-server] SQL Server: combining multiple rows into one row

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql