how to get query results as a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to get query results as a string

Author

Topic

jzurbo77
Starting Member

21 Posts

Posted - 2009-06-05 : 10:51:42

Hi,

I need to combine results coming from

SELECT GradeID FROM Grades WHERE ACTIVE='YES'

to a string like that (assuming it returns 5 values):

'201, 223, 346, 617, 895'

Any suggestions how to do this without a cursor?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 11:07:25

Maybe this example helps:

5. How to put a column into a delimited form? 
    use northwind
    Declare @ColumnList varchar(1000)
    SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
    FROM INFORMATION_SCHEMA.Columns
    WHERE table_name = 'Orders'

Source: http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 11:09:08

Here's one way:


declare @grades table (gradeid int)
insert @grades
select 201 union all
select 223 union all
select 346 union all
select 617 union all
select 895

select stuff(grades, 1, 2, '') as grades
from   (
       select ', ' + convert(varchar(10), gradeid)
       from  @grades
       for xml path('') 
       ) d (grades)

OUTPUT:
grades
---------------------------
201, 223, 346, 617, 895

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 11:13:38

webfred's method would be easier to implement over a large dataset but you would need to put his logic into a scalar UDF so that you could return the columnList (for his example) for multiple tables. The function input argument would be Table in his case - perhaps @student in you case.

Be One with the Optimizer
TG

jzurbo77
Starting Member

21 Posts

Posted - 2009-06-05 : 13:24:24

Thank you both!!

The code below does what I need:

DECLARE @grades VARCHAR(1000)
SELECT top 10 @grades = COALESCE(@grades + ', ', '') + grade
FROM grades
print replace(@grades,' ','')

not sure I understand how the "COALESCE" works but it certainly does the trick!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-05 : 13:29:03

COALESCE comes to play only for first time. initially, @grades will be null, which coalesce converts to '' string. then it keeps on adding field values from table to string with , at end. if used without colaesce, @grades will null always as concatenation of string with NULL always yields null under default conditions (i.e CONCAT NULL YIELDS NULL server setting is ON)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources