[MSSQL] STUFF

select
 (STUFF((
        SELECT ','+ convert(nvarchar,a.dept_id)
        FROM WORK a
WHERE a.comp_no = '200'
AND a.work_id IN (113985, 113984, 112848, 113843)
AND a.workpm_id IS NULL
        FOR XML PATH('')
    ), 1, 1, '')) AS 부서코드
FROM WORK x 
WHERE x.comp_no = '200'
AND x.work_id IN (113985, 113984, 112848, 113843)
AND x.workpm_id IS NULL

 

 

 

 

 

SELECT
 distinct(STUFF((
        SELECT ','+ convert(nvarchar,a.dept_id)
        FROM WORK a
WHERE a.comp_no = '200'
AND a.work_id IN (113985, 113984, 112848, 113843)
AND a.workpm_id IS NULL
        FOR XML PATH('')
    ), 1, 1, '')) AS 부서코드
FROM WORK x 
WHERE x.comp_no = '200'
AND x.work_id IN (113985, 113984, 112848, 113843)
AND x.workpm_id IS NULL

 

 

 

SELECT  
    CASE WHEN '2021-10-21'  != ''
               THEN '2021-10-21'
               ELSE SUBSTRING(MIN(x.work_date),1,4)+ '-' +
                    SUBSTRING(MIN(x.work_date),5,2)+ '-' +
                    SUBSTRING(MIN(x.work_date),7,2) 
               END
    + ' ~ ' +  
    CASE WHEN '2021-10-28' != ''
                THEN '2021-10-28'
                ELSE SUBSTRING(MAX(x.work_date),1,4)+ '-' +
                     SUBSTRING(MAX(x.work_date),5,2)+ '-' +
                     SUBSTRING(MAX(x.work_date),7,2)
                END                              AS filterDate
,COUNT(1)   AS workCnt
, (STUFF((
        SELECT DISTINCT ','+  b.DESCRIPTION
        FROM WORK a
INNER JOIN DEPT b
ON a.comp_no = b.comp_no
AND a.dept_id = b.dept_id
WHERE a.comp_no = '200'
AND a.work_idIN (113985, 113984, 112848, 113843)
AND a.workpm_id IS NULL
        FOR XML PATH('')
    ), 1, 1, ''))  AS 부서코드
FROM WORK x 
WHERE x.comp_no = '200'
AND x.work_idIN (113985, 113984, 112848, 113843)
AND x.workpm_id IS NULL