SELECT * FROM (SELECT ITEMSEQ, CUSTNAME, AVG_OUT_QTY, ROW_NUMBER() OVER (PARTITION BY ITEMSEQ ORDER BY AVG_OUT_QTY desc ) AS RN FROM (SELECT I.ITEMSEQ as ITEMSEQ , TC.CUSTNAME as CUSTNAME , SUM(I.QTY) / 6 AS AVG_OUT_QTY FROM _INVOICE M JOIN _INVOICEITEM AS I WITH(NOLOCK) ON M.InvoiceSeq = I.InvoiceSeq JOIN _SafeStokQty AS C WITH(NOLOCK) ON I.ItemSeq = C.ItemSeq join _CUST TC WITH(NOLOCK) ON M.CUSTSEQ = TC.CUSTSEQ WHERE M.INVOICEDATE BETWEEN '20190601' AND '20190831' GROUP BY I.ITEMSEQ, TC.CUSTNAME )t ) A where A.RN <= 5 order by ITEMSEQ, RN 이렇게 조회되면, ITEMSEQ 별 CUSTNAME 5개를 합쳐서 한번에 나왔으면 하는데 어떻게 해야할까요?
ORACLE에서는 WM_CONCAT 이거인거 같은데 MSSQL로 표현입니다.
SELECT STUFF((SELECT ',' + CUSTNAME
FROM (SELECT * FROM
(SELECT ITEMSEQ, CUSTNAME, AVG_OUT_QTY,
ROW_NUMBER() OVER (PARTITION BY ITEMSEQ ORDER BY AVG_OUT_QTY desc ) AS RN
FROM (SELECT I.ITEMSEQ as ITEMSEQ , TC.CUSTNAME as CUSTNAME ,
SUM(I.QTY) / 6 AS AVG_OUT_QTY
FROM INVOICE M
JOIN INVOICEITEM AS I WITH(NOLOCK) ON M.InvoiceSeq = I.InvoiceSeq
JOIN SafeStokQty AS C WITH(NOLOCK) ON I.ItemSeq = C.ItemSeq
join CUST TC WITH(NOLOCK) ON M.CUSTSEQ = TC.CUSTSEQ
WHERE
M.INVOICEDATE BETWEEN '20190601' AND '20190831'
GROUP BY I.ITEMSEQ, TC.CUSTNAME )t
) A
where A.RN <= 5)T1
WHERE ITEMSEQ = T1.ITEMSEQ
order by ITEMSEQ, RN
FOR XML PATH('')
), 1, 1, '') val
이렇게 했는데.. 한줄로 거래처명이 쭈욱 붙어 나오네요..
itemseq로 한번 group by 를 해야하는데...
어디부분에서 문제일까요 ㅠ
WITH T ( EMAIL , GRP ) AS ( SELECT '213@naver.com' , 1 UNION ALL SELECT '214@naver.com' , 1 UNION ALL SELECT '215@naver.com' , 2 UNION ALL SELECT '216@naver.com' , 2 UNION ALL SELECT '217@naver.com' , 3 ) SELECT DISTINCT STUFF((SELECT ',' + EMAIL FROM t B WHERE B.GRP = A.GRP ORDER BY EMAIL FOR XML PATH('') ), 1, 1, '') VAL FROM T A
WITH t AS ( SELECT * FROM (SELECT i.itemseq , m.custseq , tc.custname , SUM(i.qty) / 6 AS avg_out_qty , ROW_NUMBER() OVER(PARTITION BY i.itemseq ORDER BY SUM(i.qty) DESC, m.custseq) rn FROM _invoice m JOIN _invoiceitem i ON m.invoiceseq = i.invoiceseq JOIN _cust tc ON m.custseq = tc.custseq WHERE m.invoicedate BETWEEN '20190601' AND '20190831' GROUP BY i.itemseq, m.custseq, tc.custname ) a WHERE rn <= 5 ) SELECT itemseq , STUFF( (SELECT ',' + custname FROM t WHERE itemseq = m.itemseq ORDER BY rk FOR XML PATH('') ), 1, 1, '') custname , String_Agg(custname, ',') WITHIN GROUP(ORDER BY rk) custname_MSSQL_2017 FROM t m GROUP BY itemseq ORDER BY itemseq ;