BH ZHH HDMC
---------------------
1201 26001 100
1201 26002 99
1201 26003 8
1201 26004 1
1202 26001 2
1202 26002 3
1203 26001 88
1203 26003 1
如何做到以下结果ZHH HDMC(1201) HDMC(1202) HDMC(1203)
-----------------------------------------------------
26001 100 2 88
26002 99 3 *
26003 8 * 1
26004 1 * *
试了半天都没有能出来代码,求大大们帮帮忙!
pivot (max(HDMC) for BH in([1201],[1202],[1203])) as p
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([BH] INT,[ZHH] INT,[HDMC] INT)
INSERT #tb
SELECT 1201,26001,100 UNION ALL
SELECT 1201,26002,99 UNION ALL
SELECT 1201,26003,8 UNION ALL
SELECT 1201,26004,1 UNION ALL
SELECT 1202,26001,2 UNION ALL
SELECT 1202,26002,3 UNION ALL
SELECT 1203,26001,88 UNION ALL
SELECT 1203,26003,1
--------------开始查询--------------------------SELECT * FROM #tb
pivot (max(HDMC) for BH in([1201],[1202],[1203])) as p
----------------结果----------------------------
/*
ZHH 1201 1202 1203
26001 100 2 88
26002 99 3 NULL
26003 8 NULL 1
26004 1 NULL NULL
*/
AS (
SELECT 1201 , 26001 , 100
UNION ALL
SELECT 1201 , 26002 , 99
UNION ALL
SELECT 1201 , 26003 , 8
UNION ALL
SELECT 1201 , 26004 , 1
UNION ALL
SELECT 1202 , 26001 , 2
UNION ALL
SELECT 1202, 26002 , 3
UNION ALL
SELECT 1203, 26001 , 88
UNION ALL
SELECT 1203, 26003 , 1)
SELECT ZHH,[HDMC(1201)]=ISNULL(CONVERT(VARCHAR(10),SUM(CASE WHEN BH='1201' THEN HDMC END)),'*'),
[HDMC(1202)]=ISNULL(CONVERT(VARCHAR(10),SUM(CASE WHEN BH='1202' THEN HDMC END)),'*'),
[HDMC(1203)]=ISNULL(CONVERT(VARCHAR(10),SUM(CASE WHEN BH='1203' THEN HDMC END)),'*') FROM test
GROUP BY ZHH
/*
ZHH HDMC(1201) HDMC(1202) HDMC(1203)
----------- ---------- ---------- ----------
26001 100 2 88
26002 99 3 *
26003 8 * 1
26004 1 * *
警告: 聚合或其他 SET 操作消除了 Null 值。
(4 行受影响)
*/
INSERT #table
SELECT 1201,26001,100 UNION
SELECT 1201,26002,99 UNION
SELECT 1201,26003,8 UNION
SELECT 1201,26004,1 UNION
SELECT 1202,26001,2 UNION
SELECT 1202,26002,3 UNION
SELECT 1203,26001,88 UNION
SELECT 1203,26003,1SELECT ZHH,ISNULL(cast([1201] as varchar(20)),'*') as [HDMC(1201)],ISNULL(cast([1202] as varchar(20)),'*') as [HDMC(1202)],ISNULL(cast([1203] as varchar(20)),'*') as [HDMC(1203)]
FROM #table
pivot (sum(HDMC) for BH in([1201],[1202],[1203])) as a