-- 这是一个经典的行转列案例。
SELECT ORDER_NBR,
MAX(CASE
WHEN SCH_SEQ = 1 THEN
SCH_DATE
ELSE
NULL
END) AS PROCESSED,
MAX(CASE
WHEN SCH_SEQ = 2 THEN
SCH_DATE
ELSE
NULL
END) AS COMPLETED,
MAX(CASE
WHEN SCH_SEQ = 3 THEN
SCH_DATE
ELSE
NULL
END) AS CONFIRMED
FROM SERVICESSCHEDULE
GROUP BY ORDER_NBR, SERVICE_TYPE;
结果
ORDER_NBR PROCESSED COMPLETED CONFIRMED
4155526711 1994-7-16 1994-7-30
4155526710 1994-7-16 1994-7-30 1994-10-1
SELECT ORDER_NBR,
MAX(CASE
WHEN SCH_SEQ = 1 THEN
SCH_DATE
ELSE
NULL
END) AS PROCESSED,
MAX(CASE
WHEN SCH_SEQ = 2 THEN
SCH_DATE
ELSE
NULL
END) AS COMPLETED,
MAX(CASE
WHEN SCH_SEQ = 3 THEN
SCH_DATE
ELSE
NULL
END) AS CONFIRMED
FROM SERVICESSCHEDULE
GROUP BY ORDER_NBR, SERVICE_TYPE;
结果
ORDER_NBR PROCESSED COMPLETED CONFIRMED
4155526711 1994-7-16 1994-7-30
4155526710 1994-7-16 1994-7-30 1994-10-1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24558043/viewspace-675642/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24558043/viewspace-675642/