0

I have a table where it can have multiple records with same id and I want to make it single string. Here in stack overflow I found some examples which shows the use of either CAST or CASE.

I tried both of them but none of them seems to work. Meaning, query works but returns result in separate lines. I couldn't figure our what am I missing here?

Query - 1: Using CAST

SELECT DISTINCT
       c.Name AS CallCenter,
       LTRIM(RTRIM(s.Name)) Name,
       d.DNIS,
       s.ScriptId,
      CAST(sL.Line + ';' AS VARCHAR(MAX)) AS Script ,
       sL.ScriptLineId
FROM CallCenterScript s WITH (NOLOCK)
     LEFT JOIN CallCenterScriptLine sL WITH (NOLOCK) ON sL.ScriptId = s.ScriptId
     INNER JOIN CallCenterDNIS d WITH (NOLOCK) ON d.ScriptId = s.ScriptId
     INNER JOIN CallCenter c WITH (NOLOCK) ON c.Id = s.CallCenterId
WHERE (s.IsDeleted = 0 OR s.IsDeleted IS NULL)
ORDER BY DNIS ASC

Query - 2: Using CASE

DECLARE @Result VARCHAR(MAX);
SELECT DISTINCT
       c.Name AS CallCenter,
       LTRIM(RTRIM(s.Name)) Name,
       d.DNIS,
       s.ScriptId,
      CASE
          WHEN @Result IS NULL
          THEN sL.Line 
          ELSE @Result + ';' + sL.Line
       END as Script ,
       sL.ScriptLineId
FROM CallCenterScript s WITH (NOLOCK)
     LEFT JOIN CallCenterScriptLine sL WITH (NOLOCK) ON sL.ScriptId = s.ScriptId
     INNER JOIN CallCenterDNIS d WITH (NOLOCK) ON d.ScriptId = s.ScriptId
     INNER JOIN CallCenter c WITH (NOLOCK) ON c.Id = s.CallCenterId
WHERE (s.IsDeleted = 0 OR s.IsDeleted IS NULL)
ORDER BY DNIS ASC

Result: enter image description here

Sample:

enter image description here

Expected:

enter image description here

GThree
  • 2,708
  • 7
  • 34
  • 67
  • "none of them seems to work" doesn't help at all. What does not work mean??? No result??? Error out??? null?? wrong result??? – Eric Feb 26 '18 at 18:13
  • Have you tried `CONCAT()`? `CONCAT(@Result, sL.Line)` – Eric Feb 26 '18 at 18:15
  • @Eric Sorry for confusion. I have updated my question. Hope it helps. I also tried `CONCAT` but it returns same result. – GThree Feb 26 '18 at 18:20
  • I don't know what you want. Can you provide sample input and expected output? – Eric Feb 26 '18 at 18:23
  • @Eric Updated my question. – GThree Feb 26 '18 at 18:30
  • 1
    Look up `FOR XML PATH`. https://stackoverflow.com/questions/15477743/listagg-in-sqlserver – Eric Feb 26 '18 at 18:57
  • Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – S3S Feb 26 '18 at 19:11
  • @Eric Thanks for suggestion. I posted my answer below. – GThree Feb 26 '18 at 20:37

1 Answers1

0

I used FOR XML PATH and achieved what I wanted:

SELECT DISTINCT
       c.Name AS CallCenter,
       LTRIM(RTRIM(s.Name)) Name,
       d.DNIS,
       s.ScriptId,
       (
          SELECT Line + '; '
          FROM CallCenterScriptLine
          WHERE ScriptId = s.ScriptId
          ORDER BY ScriptLineId
          FOR XML PATH('')
       ) AS Script
FROM CallCenterScript s WITH (NOLOCK) 
     LEFT JOIN CallCenterScriptLine sL WITH (NOLOCK) ON sL.ScriptId = s.ScriptId
     INNER JOIN CallCenterDNIS d WITH (NOLOCK) ON d.ScriptId = s.ScriptId
     INNER JOIN CallCenter c WITH (NOLOCK) ON c.Id = s.CallCenterId
WHERE(s.IsDeleted = 0
      OR s.IsDeleted IS NULL)
ORDER BY DNIS ASC
GThree
  • 2,708
  • 7
  • 34
  • 67