[SQL] 쉼표는 SQL 쿼리로 값을 구분

쉼표는 SQL 쿼리로 값을 구분

내 SQL 테이블은 다음과 같다

City_Code     Post_Code    Post_Code_Description
100           A1           ABC
100           C8           XYZ
100           Z3           MNO
200           D4           LMN
300           E3           IJK
300           B9           RST

그것은 city_code와 Post_Code 간의 매핑입니다. 하나 City_Code 많은 포스트 코드가 있습니다. 지금은 다음과 같은 것을 얻을 수있는 쿼리를 실행하려면

City_Code     Post_Code    Post_Code_Description
100           A1,C8,Z3     ABC,XYZ,MNO
200           D4           LMN
300           E3,B9        IJK,RST

당신이 thisy SQL 테이블과 도움이 저를 기쁘게 할 것은 다음과 같다

해결법

1. ==============================

   1.이 시도:

   이 시도:

   SELECT City_Code, 
         Post_Code = 
           STUFF((SELECT ', ' + Post_Code
              FROM your_table b 
              WHERE b.City_Code = a.City_Code 
             FOR XML PATH('')), 1, 2, ''),
         Post_Code_Description=
           STUFF((SELECT ', ' + Post_Code_Description
              FROM your_table b 
              WHERE b.City_Code = a.City_Code 
             FOR XML PATH('')), 1, 2, '')
   FROM your_table a
   GROUP BY City_Code
   

2. ==============================

   2.당신이 MySQL을 사용하는 경우는) (GROUP_CONCAT을 사용할 수 있습니다

   당신이 MySQL을 사용하는 경우는) (GROUP_CONCAT을 사용할 수 있습니다

   select City_Code, 
        GROUP_CONCAT(Post_Code) Post_Code, 
        GROUP_CONCAT(Post_Code_Description) post_code_description
   from yourtable
   group by City_Code
   

   SQL Server의 경우는 다도 () 및 XML 경로를 ()를 사용할 수 있습니다

   select city_code,
       Stuff((SELECT ', ' + post_code 
               FROM yourtable t2
               where t1.city_code = t2.city_code
               FOR XML path('')),1,1,'') Post_Code,
       Stuff((SELECT ', ' + post_code_description
               FROM yourtable t2
               where t1.city_code = t2.city_code
               FOR XML path('')),1,1,'') post_code_description
   from yourtable t1
   group by city_code
   

3. ==============================

   3.이 시도:

   이 시도:

   select city_code,substring((select ',' + post_code  
   from city b where a.city_code=b.city_code
   for xml path('')
   ),2,100)as post_code,
   substring((select ',' + post_code_description 
   from city c where a.city_code=c.city_code
   for xml path('')
   ),2,100)as post_code_description
   from city a
   group by a.city_code
   

4. ==============================

   4.이것에 대한 재귀 쿼리를 사용합니다 :

   이것에 대한 재귀 쿼리를 사용합니다 :

   --Prepare Dummy Data
   ;WITH CITIES 
        AS (SELECT 100   AS City_Code, 
                   'A1'  AS Post_code, 
                   'ABC' AS Post_Code_Description 
            UNION 
            SELECT 100   AS City_Code, 
                   'C8'  AS Post_code, 
                   'XYZ' AS Post_Code_Description 
            UNION 
            SELECT 100   AS City_Code, 
                   'Z3'  AS Post_code, 
                   'MNO' AS Post_Code_Description 
            UNION 
            SELECT 200   AS City_Code, 
                   'D4'  AS Post_code, 
                   'LMN' AS Post_Code_Description 
            UNION 
            SELECT 300   AS City_Code, 
                   'E3'  AS Post_code, 
                   'IJK' AS Post_Code_Description 
            UNION 
            SELECT 300   AS City_Code, 
                   'B9'  AS Post_code, 
                   'RST' AS Post_Code_Description), 
   --Add Row numbers to each row
        PREPARE 
        AS (SELECT *, 
                   ROW_NUMBER () 
                     OVER ( 
                       PARTITION BY CITY_CODE 
                       ORDER BY CITY_CODE) RN 
            FROM   CITIES),
   --Start Recursive CTE 
        RECURSIVE 
        AS (
   --Anchor Query
            SELECT CITY_CODE, 
                   CAST(POST_CODE AS VARCHAR(MAX))             Post_code, 
                   CAST(POST_CODE_DESCRIPTION AS VARCHAR(MAX)) 
                   Post_Code_Description, 
                   1                                           AS LEVEL, 
                   RN 
            FROM   PREPARE 
            WHERE  RN = 1 
            UNION ALL 
   --Recursive Query
            SELECT T1.CITY_CODE, 
                   T1.POST_CODE + ',' + T2.POST_CODE, 
                   T1.POST_CODE_DESCRIPTION + ',' 
                   + T2.POST_CODE_DESCRIPTION, 
                   T2.LEVEL + 1, 
                   T1.RN 
            FROM   PREPARE AS T1 
                   INNER JOIN RECURSIVE AS T2 
                           ON T1.RN = T2.RN + 1 
                              AND T1.CITY_CODE = T2.CITY_CODE) 
   --Final Results
   SELECT T1.CITY_CODE, 
          T1.POST_CODE, 
          T1.POST_CODE_DESCRIPTION 
   FROM   RECURSIVE T1 
          INNER JOIN (SELECT CITY_CODE, 
                             COUNT(*) cnt 
                      FROM   CITIES 
                      GROUP  BY CITY_CODE)T2 
                  ON T1.CITY_CODE = T2.CITY_CODE 
   WHERE  T1.LEVEL = T2.CNT 
   

from https://stackoverflow.com/questions/12671117/comma-separated-values-with-sql-query by cc-by-sa and MIT license