꿈꾸는 개발자, DBA 커뮤니티 구루비

문자 합쳐서 표현하기

by 느림보 [SQLServer] [2019.09.20 14:22:33]

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로 표현입니다.

by jkson [2019.09.20 14:44:44]

http://www.gurubee.net/article/55512

댓글에 mssql 있어요~

by 우리집아찌 [2019.09.20 14:46:18]

http://www.gurubee.net/article/79757

by 느림보 [2019.09.20 15:05:20]

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 를 해야하는데...

어디부분에서 문제일까요 ㅠ

by 우리집아찌 [2019.09.20 15:14:41]

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

by 마농 [2019.09.20 16:14:39]

2017 버전이면 String_Agg 사용하면 간단합니다.

by 마농 [2019.09.20 16:55:20]

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
;

by 느림보 [2019.09.20 16:59:33]

아.. 버전때문에 String_Agg 에 먹히지 않아서 고생했네요 ㅠㅠ

다른 형식으로 풀어서 했습니다. 정말 감사합니다. 아.. 오라클이 정말 간절하네요

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변