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 ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT 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)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO
Here is the result set:
Total Salary
------------
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000