Sqler 에 올라온 질문 글...

 

http://www.sqler.com/876327#0

 

 

string 파싱하는데 늦다는 거였음.

 

12263,2,3,18321,2,1,19323,3,2,23152,5,4,27354,4,5  요런 문자열을 key1, key2, key3으로 분해하여 테이블형태로 변환하는 문제.

 

 string str = "12263,2,3,18321,2,1,19323,3,2,23152,5,4,27354,4,5";
            DataTable dt = new DataTable();
            dt.Columns.Add("Key1");
            dt.Columns.Add("Key2");
            dt.Columns.Add("Key3");

            string[] data = str.Split(',');
            for (int loop = 0; loop < data.Length; loop += dt.Columns.Count)
            {
                DataRow newRow = dt.NewRow();
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    newRow[col] = data[(dt.Rows.Count * dt.Columns.Count) + col];
                }
                dt.Rows.Add(newRow);
            }

 

요렇게 하면... 빠른데... 얼마나 많길래... 느리다고 하는 걸까? 거기에 xml파싱? 인가로 하면 더 빠를까?

 

 

음... 다시 가보니 쿼리로 풀어진 답글들이 있다..

 

그래서 나도...

 

declare @data xml = '<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">' +
                             '<num>' + Replace( '12263,2,3,18321,2,1,19323,3,2,23152,5,4,27354,4,5',',','</num><num>')+  '</num>' +
                             '</ROOT>'
 
SELECT  ( [key] - 1 ) / 3 AS ord
       ,sum( CASE WHEN [key] % 3 = 1 THEN [value] END ) AS key1
       ,sum( CASE WHEN [key] % 3 = 2 THEN [value] END ) AS key2
       ,sum( CASE WHEN [key] % 3 = 0 THEN [value] END ) AS key3
  FROM  (
            SELECT  ROW_NUMBER() OVER( ORDER BY num1 ,num2 ,num3 ) AS [key]
                   ,M.tb.query( '.' )  .value( '.' , 'int' ) AS [value]
              FROM  @data.nodes( '/ROOT/num' ) AS M ( tb )
            ,(
                 SELECT  1 num1
                        ,2 num2
                        ,3 num3
             )AS nums
        ) tot
 GROUP  BY ( [key] - 1 ) /3