Generate string from SQL Query

Question

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

Is the output something you want to produce out of SQL or can it be done programatically? — nageeb
– nageeb, Commented Jun 7, 2012 at 6:02

Aaron Bertrand · Accepted Answer · 2022-01-24 17:50:56Z

2

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
  );

edited Jan 24, 2022 at 17:50

answered Jun 7, 2012 at 6:20

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Phil · Accepted Answer · 2012-06-07 06:14:42Z

1

declare @result varchar(max) = ''

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

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

edited Jun 7, 2012 at 6:14

answered Jun 7, 2012 at 6:06

Phil

43.2k9 gold badges102 silver badges102 bronze badges

1 Comment

Aaron Bertrand Over a year ago

FWIW STUFF against a leading comma is a much more effective way to remove it than using LEFT against a trailing comma. Why? Well, you don't have to calculate the length, for one. :-) In this case it is negligible but on a larger data set that is a lot of extra cycles for nothing.

Collectives™ on Stack Overflow

Generate string from SQL Query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related