实现数据行转列(数据库必须最低兼容90以上)
DECLARE @SQLText NVARCHAR(4000)
SET @SQLText = ''
DECLARE @ResultColumn_Char VARCHAR(8000)
SET @ResultColumn_Char = ''
DECLARE @ResultColumn TABLE
(
SerialNo INT IDENTITY ,
ResultColumn VARCHAR(255)
)
INSERT INTO @ResultColumn
( ResultColumn )
SELECT DISTINCT ParameterName
FROM dbo.tb_ChargeBalanceDetail
WHERE OperationID = 'Charge_Clinic'
DECLARE @iCounter INT
DECLARE @iCounter_max INT
SELECT @iCounter = MIN(SerialNo),@iCounter_max = MAX(SerialNo) FROM @ResultColumn
WHILE @iCounter <= @iCounter_max BEGIN
SELECT @ResultColumn_Char = @ResultColumn_Char + CASE WHEN RTRIM(@ResultColumn_Char) <> '' THEN + ',' ELSE '' END + '[' + ResultColumn + ']' FROM @ResultColumn WHERE SerialNo = @iCounter
SET @iCounter = @iCounter + 1
END
SET @SQLtext ='
SELECT *
FROM
(SELECT ChargeBillID,ParameterName,ParameterValue
FROM tb_ChargeBalanceDetail WHERE OperationID = ''Charge_Clinic'') p
PIVOT
(
SUM(p.ParameterValue)
FOR ParameterName IN
( ' + @ResultColumn_Char +
') AS pvt
'
SELECT @SQLText
--EXEC (@SQLText)
数据行合并列
SELECT HospitalID,DiagnosisName = STUFF((SELECT ',' + DiagnosisName FROM dbo.tb_EMR_TRD_DiagnosticRecordsInFormation WHERE HospitalID = T1.HospitalID FOR XML PATH('')),1,1,'')
FROM dbo.tb_EMR_TRD_DiagnosticRecordsInFormation AS T1
