Group columns into multiple rows and Group_concate

问题:

I’ve been looking for a way to show one column in multiple rows, one cell. The content of it separated by comma’s.

For example, in stead of:

ProjectID  Name                 count
-------------------------------------
   2       Technical Services    31
   1       Security Services     32
   7       Technical Services    32

I would like the result of my query to look like this:

Name                 Label
---------------------------
Technical Services   31,2
Technical Services   32,7
Security Services    32,1 

I also want the result of my query to look like this: (like Group_Concate in MySQL)

Name                 Label
-------------------------------
Security Services    32,1 
Technical Services   31,2: 32,7

回答1:

Try this:

SELECT  Name, 
CAST(count AS VARCHAR(10)) + ',' + CAST(ProjectID AS VARCHAR(10))
AS Label FROM table1

Result

NAME                   LABEL
----------------------------
Security Services      32,1
Technical Services     32,7
Technical Services     31,2

See this SQLFiddle

If you want to group by Name (Something like Group_Concate in MySQL) there is no any method for it in SQL Server. It's just a logic behind it.
So try this:

SELECT  * FROM    (
    SELECT  DISTINCT Name
    FROM    table1
    ) table2
CROSS APPLY
    (SELECT  CASE ROW_NUMBER() 
      OVER(ORDER BY ProjectId) 
      WHEN 1 THEN '' ELSE ': ' END + 
      (CAST(table3.count AS VARCHAR(10)) + ',' 
      + CAST(table3.ProjectID AS VARCHAR(10)) )
    FROM    table1 table3
    WHERE   table3.Name = table2.Name
    ORDER BY ProjectID
    FOR XML PATH ('')
    ) table3(Label)

So the result will be

NAME                  LABEL
--------------------------------
Security Services     32,1
Technical Services    31,2: 32,7

See this SQLFiddle

回答2:

select  Name, 
        cast(count as varchar(5))+','+cast(ProjectID as varchar(5)) as label
from <table>