sqlserver合并拆分数据

SELECT
	t.CLOCK_IN_DATE,
	t.AUTO_CODE,
	SUM ( one ) one,
	SUM ( two ) two,
	SUM ( three ) three ,
	sum(one+two+three)
FROM
	(
	SELECT SUBSTRING
		( asw.CLOCK_IN_DATE, 0, 11 ) CLOCK_IN_DATE,
		asw.AUTO_CODE,
	CASE
			
			WHEN gqs.recordType = 0 THEN
			CONVERT ( DECIMAL ( 9, 1 ), gqs.deskTotal ) ELSE 0 
		END one,
CASE
	
	WHEN gqs.recordType = 1 THEN
	CONVERT ( DECIMAL ( 9, 1 ), gqs.deskTotal ) ELSE 0 
	END two,
CASE
	
	
	WHEN gqs.recordType = 2 THEN
	CONVERT ( DECIMAL ( 9, 1 ), gqs.deskTotal ) ELSE 0 
	END three 
FROM
	AFFOREST_SUPPLY_WATER asw
	LEFT JOIN greenQsSetting gqs ON asw.waterClass= gqs.id 
WHERE
	asw.waterClass IS NOT NULL 
GROUP BY
	SUBSTRING ( asw.CLOCK_IN_DATE, 0, 11 ),
	asw.AUTO_CODE,
	asw.waterClass,
	gqs.recordType,
	gqs.deskTotal 
	) t 
GROUP BY
	t.CLOCK_IN_DATE,
	t.AUTO_CODE