將多行數列的值變成橫列,並且拼接到一個欄位中，一下是建立了一個作業，存入其中呼叫，方便呼叫。
工具/原料
Sql Server2000
方法/步驟
//如果有該作業，想要修改，需先刪除作業。
--DROP FUNCTION dbo.FW_HospitalizationCost
//建立了一個作業，設定想要存入的引數
CREATE FUNCTION dbo.FW_HospitalizationCost(@PatientID nvarchar(50),@Name nvarchar(50),@PatienRecordNo nvarchar(50),@Mobile nvarchar(50),@OwnHospitalTimes nvarchar(50))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
//建立臨時引數
SET @r = ''
//一下為一個組合查詢，查詢並賦值
SELECT @r = @r + ',' + TheCostName + ':' + TheCostMoney
FROM (select FH.PatientID,CASE ISNULL(FH.Name,'') WHEN '' THEN FP.Name ELSE FH.Name END AS Name,CASE ISNULL(FH.PatienRecordNo,'') WHEN '' THEN FP.PatienRecordNo ELSE FH.PatienRecordNo END AS PatienRecordNo,CASE ISNULL(FH.Mobile,'') WHEN '' THEN CASE ISNULL(FP.Mobile,'') WHEN '' THEN ISNULL(FP.RprTel,'') WHEN '0' THEN ISNULL(FP.RprTel,'') END ELSE FH.Mobile END AS Mobile,FH.OwnHospitalTimes,TheCostName,TheCostMoney
FROM FWD_HospitalizationCost AS FH,FWD_Patient AS FP
WHERE FH.PatientID = FP.PatientID AND FP.OwnHospitalTimes = FH.OwnHospitalTimes) as PP
WHERE [email protected] and @Name = Name and @PatienRecordNo = PatienRecordNo and @Mobile = Mobile and @OwnHospitalTimes = OwnHospitalTimes
RETURN STUFF(@r, 1, 1, '')
END
GO
