[MS SQL]SQL Script Group by pivot

線上SQL Script 測試  http://sqlfiddle.com/

WITH tmp
AS
(SELECT
A.GROUP_ID
   ,C.GROUP_NAME
   ,B.USER_ID
FROM USER_GROUP_RELATION A
LEFT JOIN USER_PROFILE B
ON A.user_id = B.user_id
LEFT JOIN GROUP_SET C
ON A.GROUP_ID = C.GROUP_ID
)
SELECT
user_id
   ,SUM(CASE
WHEN group_id = 'CBMS001' THEN 1
ELSE 0
END) [GROUP001]
   ,SUM(CASE
WHEN group_id = 'GROUP002' THEN 1
ELSE 0
END) [GROUP002]
   ,COUNT(*) [ALL]
FROM tmp
GROUP BY user_id;