using COALESCE() 分組結果顯示成一條記錄

1 將分組結果顯示成一條記錄，並且加入分隔字元 “，”

CREATE               PROCEDURE dbo.RestaurantInfo_KindCount
AS
BEGIN TRAN
declare @Counts int
declare @ArraryCount Nvarchar(100)
declare @ArraryID Nvarchar(100)

      select @ArraryCount=''
      select @ArraryID=''
      select @ArraryCount=@ArraryCount+COALESCE(Convert(Nvarchar,Count(1)) + ',', ' '),@ArraryID=@ArraryID+COALESCE(Type + ',', ' ') from T_Ad_RestaurantInfo group by Type
      select @ArraryCount as ArraryCount,@ArraryID as ArraryID

IF @@ERROR!=0
    BEGIN
        ROLLBACK TRAN
        RAISERROR 20000 'p_soofan_Object_ProjectList: Error'
        RETURN(1)
    END
COMMIT TRAN

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Examples from SQL Server Books online

In this example, the wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.

SET NOCOUNT ONGOUSE masterIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES       WHERE TABLE_NAME = 'wages')   DROP TABLE wagesGOCREATE TABLE wages(   emp_id      tinyint    identity,   hourly_wage   decimal   NULL,   salary      decimal    NULL,   commission   decimal   NULL,   num_sales   tinyint   NULL)GOINSERT wages VALUES(10.00, NULL, NULL, NULL)INSERT wages VALUES(20.00, NULL, NULL, NULL)INSERT wages VALUES(30.00, NULL, NULL, NULL)INSERT wages VALUES(40.00, NULL, NULL, NULL)INSERT wages VALUES(NULL, 10000.00, NULL, NULL)INSERT wages VALUES(NULL, 20000.00, NULL, NULL)INSERT wages VALUES(NULL, 30000.00, NULL, NULL)INSERT wages VALUES(NULL, 40000.00, NULL, NULL)INSERT wages VALUES(NULL, NULL, 15000, 3)INSERT wages VALUES(NULL, NULL, 25000, 2)INSERT wages VALUES(NULL, NULL, 20000, 6)INSERT wages VALUES(NULL, NULL, 14000, 4)GOSET NOCOUNT OFFGOSELECT CAST(COALESCE(hourly_wage * 40 * 52,    salary,    commission * num_sales) AS money) AS 'Total Salary' FROM wagesGO

Here is the result set:

Total Salary ------------ 20800.000041600.000062400.000083200.000010000.000020000.000030000.000040000.000045000.000050000.0000120000.000056000.0000