22,206
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(NAME VARCHAR(10),
TV INT,
TYPES VARCHAR(10),
SVALUE VARCHAR(10))
INSERT INTO #T
SELECT 'A',1,'REST','A1' UNION ALL
SELECT 'A',2,'WORK','A2' UNION ALL
SELECT 'A',3,'REST','A3' UNION ALL
SELECT 'A',4,'WORK','A4' UNION ALL
SELECT 'A',5,'REST','A5' UNION ALL
SELECT 'A',6,'WORK','A6' UNION ALL
SELECT 'A',7,'REST','A7' UNION ALL
SELECT 'B',1,'REST','B1' UNION ALL
SELECT 'B',2,'WORK','B2' UNION ALL
SELECT 'B',3,'REST','B3' UNION ALL
SELECT 'C',1,'WORK','C1' UNION ALL
SELECT 'C',2,'REST','C2' UNION ALL
SELECT 'C',3,'REST','C3'
--如果T1到T7字段是固定显示的,那就用以下的静态语句
SELECT NAME,
MAX(CASE WHEN TV=1 THEN SVALUE ELSE '' END) AS T1,
MAX(CASE WHEN TV=2 THEN SVALUE ELSE '' END) AS T2,
MAX(CASE WHEN TV=3 THEN SVALUE ELSE '' END) AS T3,
MAX(CASE WHEN TV=4 THEN SVALUE ELSE '' END) AS T4,
MAX(CASE WHEN TV=5 THEN SVALUE ELSE '' END) AS T5,
MAX(CASE WHEN TV=6 THEN SVALUE ELSE '' END) AS T6,
MAX(CASE WHEN TV=7 THEN SVALUE ELSE '' END) AS T7
FROM #T
GROUP BY NAME
--如果字段的个数是可变的,那就用以下的动态语句
DECLARE @SQL VARCHAR(8000)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN TV='+CAST(TV AS VARCHAR)+' THEN SVALUE ELSE '''' END) AS T'+CAST(TV AS VARCHAR)
FROM
(SELECT TV FROM #T GROUP BY TV) AS A
ORDER BY TV
SET @SQL='SELECT NAME,'+@SQL+' FROM #T GROUP BY NAME'
EXEC(@SQL)