데이터베이스 질문과 답변
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로 표현입니다.
http://www.gurubee.net/article/55512
댓글에 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
2017 버전이면 String_Agg 사용하면 간단합니다.
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
;
아.. 버전때문에 String_Agg 에 먹히지 않아서 고생했네요 ㅠㅠ
다른 형식으로 풀어서 했습니다. 정말 감사합니다. 아.. 오라클이 정말 간절하네요
) 버튼을 클릭하여 작성 하시면 됩니다.