데이터베이스 질문과 답변

문자 합쳐서 표현하기 0 7 1,305

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

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

 

by jkson [2019.09.20 14:44:44]

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

댓글에 mssql 있어요~

by 우리집아찌 [2019.09.20 14:46:18]
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]
?
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
by 마농 [2019.09.20 16:14:39]

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

by 마농 [2019.09.20 16:55:20]
?
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
;

 

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

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

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

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입