Have Geek Brain

How to format TSQL SELECT output in SQL Sever

How do I scroll through the results of a select query to have rich text? for example, the selection looks like this:

select name from table

and we want the @names variable:

"name1,name2,name3"

The database is SQL Server 2005

+2


a source to share


3 answers


If the table contains multiple records, that is:

1, name1, ..
2, name2, ..
3, name3, ..

then this query:

DECLARE @names VARCHAR(MAX)
SELECT @names = COALESCE(@names + ', ', '') + name
FROM table


will produce the following result:

name1, name2, name3

See COALESCE on MSDN

+5


a source


This needs to be done within the function. There is no quick way to do this. You usually do this in your client code.



+1


a source


If you plan to do a function that you do on each line, form a different request, it will be very slow because the function must be called on each line. You have to work with datasets in one go, it executes all rows in one go. This is an example of how to concatenate multiple values ​​for each line of a different query:

set nocount on;
declare @t table (id int, name varchar(20), x char(1))
insert into @t (id, name, x)
select 1,'test1', 'a' union
select 1,'test1', 'b' union
select 1,'test1', 'c' union
select 2,'test2', 'a' union
select 2,'test2', 'c' union
select 3,'test3', 'b' union
select 3,'test3', 'c' 

SELECT p1.id, p1.name,
          stuff(
                   (SELECT
                        ', ' + x
                        FROM @t p2
                        WHERE p2.id=p1.id
                        ORDER BY name, x
                        FOR XML PATH('') 
                   )
                   ,1,2, ''
               ) AS p3
      FROM @t p1
     GROUP BY 
        id, name

OUTPUT:

id          name                 p3
----------- -------------------- -----------
1           test1                a, b, c
2           test2                a, c
3           test3                b, c
+1


a source






More articles: