1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 를 해야하는데...
어디부분에서 문제일까요 ㅠ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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 ; |