2

This is the current result that can be changed from day to day

    (int)   (nvarchar)
    Number   Grade
    --------------
         1       a
         1       c
         2       a
         2       b
         2       c
         3       b
         3       a

What I need help is to achieve this result below.

Number      Grade
-----------------
     1       a, c
     2    a, b, c
     3       b, a
Improve this question
1

2 Answers 2

Reset to default
5

Use:

declare @t table(Number int, Grade varchar)

insert @t values(1, 'a'), (1, 'c'), (2, 'a'), (2, 'b'), (2, 'c'),
(3, 'b'), (3, 'a')

select t1.Number
    , stuff((
        select ',' + Grade
        from @t t2
        where t2.Number = t1.Number
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') [values]
from @t t1
group by t1.Number
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Where can I read more about ").value('.', 'varchar(max)')" and "[values]" to understand more of the these syntax?
@FullmetalBoy – the subquery creates XML and value is used to get the value of the XML. Read more about value here.
There are some addition al question that I don't understand. Q1 = why using and how come to use "." from "value('.'"?
@DWD, What do you mean? . is XPath expression which selects XML context node: w3.org/TR/xpath/#path-abbrev
I believe you can drop the TYPE and subsequent .value() invocation. The TYPE directive casts the output as XML, which then requires you to extract the value you need with .value(). Without it, you can directly manipulate the output of the FOR XML query.
4

You'll need to replace dbo.tablename with your actual table. Also I'm assuming you're using SQL Server 2005 or better - always useful to specify.

SELECT Number, Grades = STUFF((
    SELECT N', ' + Grade FROM dbo.tablename
    WHERE Number = x.Number 
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tablename AS x
GROUP BY Number;

In SQL Server 2017 and Azure SQL Database, you can use the new aggregation function STRING_AGG(), which is a lot tidier in this case:

SELECT Number, Grades = STRING_AGG(Grade, N', ')
  FROM dbo.tablename
  GROUP BY Number;
Improve this answer

11 Comments

Using distinct for these kinds of queries is not optimal. Using group by as in the answer provided by Kirill Polishchuk is the faster alternative. Here the comma separated string is built once for each row instead of once for each group of rows.
@MikaelEriksson please note that in my case against sys.all_columns I actually get much better performance with DISTINCT compared to GROUP BY (latter is more than 10x longer in elapsed time). Have you done any specific tests on this, or is it theoretical?
I did some tests on the structure used here with a table filled with spt_values x 3. The result was some 4500 rows grouped. Distinct query took 600 ms and group took 300. This was on SQL server 2012. The diff in my test was bigger on 2008 but that was also on a different computer so no real conclusion there. There was also a big difference in reads on a work table.
@MikaelEriksson Well jury's still out IMHO. I see more reads on the worktable of course but I can't ignore such a significant (and repeatable, even with dropcleanbuffers/freeproccache) delta in cpu/elapsed time. This was on SQL Server 2012 as well. 640 rows grouped in this case.
@MikaelEriksson it does make sense that it would be more efficient. I'll have to remember this, in a lot of cases I've found that the two were interchangeable but in this case it is clear why they actually would behave differently.
|