[MS SQL]不足左補0

select RIGHT('0000000000' + CAST(123 as VARCHAR),10) ;

select REPLICATE('0', (10-LEN('123')))+'123';

select RIGHT(REPLICATE('0',10) + CAST(123 as VARCHAR),10);

PS. Oracle 用 LPAD/RPAD即可完成

[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;