CODEWITHSUNDEEP
sql-server
Mohammed Faruk
Mohammed Faruk

Reputation: 495

Generate string from SQL Query

I have following query:

SELECT ChallanNo+'-'+CONVERT(VARCHAR(12),ChallanDate,106) 
   FROM Challan WHERE ChallanID IN 
   (SELECT ChallanID FROM ChallanDetail WHERE PTUID=42192)

This query result will be : 

151468-29 May 2012
151591-31 May 2012

Now I need a string like this : 

151468-29 May 2012, 151591-31 May 2012

Thanks for response

Upvotes: 0

Views: 232

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

On versions of SQL Server < 2017, you had to use STUFF and a FOR XML PATH subquery.

SELECT STUFF((SELECT ', ' + CONVERT(VARCHAR(32), ChallanNo) 
  + '-' + CONVERT(CHAR(11), ChallanDate, 106)  
  FROM dbo.Challan WHERE ChallanID IN 
   (SELECT ChallanID FROM dbo.ChallanDetail WHERE PTUID = 42192)
FOR XML PATH(''), 
TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 2, '');

In 2017+, you should definitely switch to STRING_AGG():

SELECT STRING_AGG(CONCAT(c.ChallanNo, '-',
    CONVERT(char(11), c.ChallanDate, 106)), ', ')
  FROM dbo.Challan AS c
  WHERE EXISTS 
  (
    SELECT 1 FROM dbo.ChallanDetail AS cd
    WHERE cd.ChallanID = c.ChallanID
    AND cd.PTUID = 42192
  );

Upvotes: 2

Phil
Phil

Reputation: 42991

declare @result varchar(max) = ''

select @result = @result + ChallanNo + '-' + 
                 CONVERT(VARCHAR(12),ChallanDate,106) + ','
from ...

-- remove trailing ,
select left(@result, len(@result) - 1)

Upvotes: 1

Related Questions