Unable to join multiple rows into single by using CAST or CASE in SQL Server query

Question

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:

Sample:

Expected:

"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
@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
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

score 0 · Answer 1 · answered Feb 26 '18 at 20:36

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

Unable to join multiple rows into single by using CAST or CASE in SQL Server query

1 Answers1