WITH SR_LIST AS(
SELECT
DISTINCT
T0.CM_CD_KEY UDR_ID,
T2.STD_DATE ,
T2.DAY_NO
FROM T_CM_CM_CD T0
LEFT JOIN T_SR_DIST T1 ON T1.UDR_ID=T0.CM_CD_KEY AND CONVERT(VARCHAR(8),T1.PLAN_START_DT,112)>= '20150101'
LEFT JOIN MASTER_STANDARD_DATE T2 ON CONVERT(VARCHAR(8),PLAN_START_DT,112) <= T2.STD_DATE
AND CONVERT(VARCHAR(8),PLAN_END_DT,112) >= T2.STD_DATE
WHERE 1=1
AND T0.GRP_CD='G000192'
AND T0.USE_YN='Y'
)
SELECT
T1.UDR_ID,
SUM(CASE
WHEN T1.STD_DATE = '20150101' AND T1.DAY_NO < '7' THEN 1
ELSE 0
END) '20150101',
SUM(CASE
WHEN T1.STD_DATE = '20150102' THEN 1
ELSE 0
END) '20150102',
SUM(CASE
WHEN T1.STD_DATE = '20150103' THEN 1
ELSE 0
END) '20150103',
SUM(CASE
WHEN T1.STD_DATE = '20150104' THEN 1
ELSE 0
END) '20150104',
SUM(CASE
WHEN T1.STD_DATE = '20150105' THEN 1
ELSE 0
END) '20150105',
SUM(CASE
WHEN T1.STD_DATE = '20150106' THEN 1
ELSE 0
END) '20150106',
SUM(CASE
WHEN T1.STD_DATE = '20150107' THEN 1
ELSE 0
END) '20150107',
SUM(CASE
WHEN T1.STD_DATE = '20150108' THEN 1
ELSE 0
END) '20150108',
SUM(CASE
WHEN T1.STD_DATE = '20150109' THEN 1
ELSE 0
END) '20150109',
SUM(CASE
WHEN T1.STD_DATE = '20150110' AND T1.DAY_NO = '7' THEN 9
WHEN T1.STD_DATE = '20150110' AND T1.DAY_NO < '7' THEN 1
WHEN T1.STD_DATE='20150110' AND T1.DAY_NO='7' AND T1.UDR_ID IS NULL THEN 9
ELSE 0
END) '20150110',
SUM(CASE
WHEN T1.STD_DATE = '20150111' AND T1.DAY_NO = '7' THEN 9
WHEN T1.STD_DATE = '20150111' AND T1.DAY_NO < '7' THEN 1
ELSE 0
END) '20150111',
SUM(CASE
WHEN T1.STD_DATE = '20150112' THEN 1
ELSE 0
END) '20150112'
FROM SR_LIST T1
GROUP BY T1.UDR_ID
------
1.主要是为了做一个稼动率统计，将一个月的每天去循环和数据中的STD_DATE比较，如果存在，则为1，表示工作，并且还要去掉周末，如果不存在，则为0，也要去掉周末
2.类似行转列问题，但是并不一样，首先，根据月份动态添加列，然后再用这里列去比较已存在的列
2.如何将上面的sum（case when T1.STD_DATE=日期参数 AND T1.DAY_NO='7' THEN 9
WHEN T1.STD_DATE = 日期参数 AND T1.DAY_NO = '7' THEN 9
WHEN T1.STD_DATE = '日期参数 AND T1.DAY_NO < '7' THEN 1
ELSE 0
END) 日期参数
动态写入，根据月份产生动态列
------解决思路----------------------
请提供例子数据和预期结果，应该可以用动态语句拼出来的。
------解决思路----------------------
我觉得是动态行转死，如果给示例数据，然后再给符合示例数据的期望结果，旁边可能会更清楚点