복붙노트

[SQL] 다수의 열 값으로 나누어 하나의 열 값

SQL

다수의 열 값으로 나누어 하나의 열 값

내가 구독의 테이블을 가지고 가입 번호는 실제로 하나 개의 컬럼에 저장 한 값입니다. 우리는 다음과 같은 샘플 값이

SC 5-1395-174-25P 
SC 1-2134-123-ABC C1-2
SC 12-5245-1247-14&P
SC ABCD-2525-120

그래서 우리는이 개별 컬럼으로 분리해야합니다. 네 위는 다음과 같은 방법을 분할해야한다 그래서

**Col1**   **Col2**   **Col3**   **Col4**  **Col5**  **Col6**   **Col7**
**SC**      **5**     **1395**   **174**   **25P** 
**SC**      **1**     **2134**   **123**   **ABC**   **C1**      **2**
**SC**      **12**    **5245**   **1247**  **14&P**
**SC**      **ABCD**  **2525**   **120**

해결법

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

    1.여기에 인라인 (in-line) 방식이다

    여기에 인라인 (in-line) 방식이다

    예

    Declare @YourTable table (SomeCol varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')


Select B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.SomeCol,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

    보고

    1) 귀하의 테이블 만들기

    CREATE TABLE MyNewPubTable (PUB_FORM_NUM NVARCHAR(50) , COL1 NVARCHAR(10) , COL2 NVARCHAR(10) , COL3 NVARCHAR(10) , COL4 NVARCHAR(10) , COL5 NVARCHAR(10) , COL6 NVARCHAR(10) , COL7 NVARCHAR(10))

    2) 쿼리 실행

    Declare @YourTable table (PUB_FORM_NUM varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')

Insert Into MyNewPubTable
Select A.PUB_FORM_NUM
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.PUB_FORM_NUM,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

    3) 결과를 검토

    Select * From MyNewPubTable

    아니면 즉석에서 테이블을 만들 수 있습니다

    Declare @YourTable table (PUB_FORM_NUM varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')

Select A.PUB_FORM_NUM
      ,B.*
 Into  MyNewPubTable
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.PUB_FORM_NUM,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Select * From MyNewPubTable
  2. ==============================

    2.SQL 서버에서 2016+는 string_split ()을 사용할 수 있습니다.

    SQL 서버에서 2016+는 string_split ()을 사용할 수 있습니다.

    SQL 서버에서 사전 2016, 제프 MODEN하여 CSV 분배기 테이블 반환 함수를 사용하여 :

    select 
    id, Col1 = [1], Col2 = [2], Col3 = [3], Col4 = [4], Col5 = [5], Col6 = [6], Col7 = [7] 
from t
  cross apply dbo.DelimitedSplit8k(replace(col,'-',' '),' ') s
  pivot (max(Item) for ItemNumber in ([1], [2], [3], [4], [5], [6], [7])) p
order by id

    れ x て s て r도 : h っ tp : // 져서 x て s て r. 이 m / P 아니 F36892

    보고:

    +----+------+------+------+------+------+------+------+
| id | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 |
+----+------+------+------+------+------+------+------+
|  1 | SC   | 5    | 1395 |  174 | 25P  | NULL | NULL |
|  2 | SC   | 1    | 2134 |  123 | ABC  | C1   | 2    |
|  3 | SC   | 12   | 5245 | 1247 | 14&P | NULL | NULL |
|  4 | SC   | ABCD | 2525 |  120 | NULL | NULL | NULL |
+----+------+------+------+------+------+------+------+

    분할 문자열 참조 :

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

    3.UPDATE : 최종 쿼리 포함

    UPDATE : 최종 쿼리 포함

    데이터가 테이블에 살고 있기 때문에 당신은 값을 분할하는 인덱싱 된 뷰를 사용할 수 있습니다. 뭐?!?! 그래, 내가 약 누구인지 당신이 빠른 DelimitedSplit8K, string_split 또는 거기 어떤 CLR보다 표시합니다. 여기에 우리가 그것을 해낼 방법입니다.

    / 채우기 귀하의 테이블과 실제 탈리 표 (일명 "번호 테이블을") 만들기 1

    (주하는 "CTE 집계 / 번호 테이블"것 이것에 대한 NOT 일).

    USE tempdb
GO

-- 1. Create/Populate your table and a real Tally Table
IF OBJECT_ID('dbo.yourView') IS NOT NULL DROP VIEW dbo.yourView;
IF OBJECT_ID('dbo.tally') IS NOT NULL DROP TABLE dbo.tally;
CREATE TABLE dbo.tally
(
  N int NOT NULL,
  CONSTRAINT pk_tally PRIMARY KEY CLUSTERED(N) ,
  CONSTRAINT uq_tally UNIQUE NONCLUSTERED(N)
);
GO
IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable;
CREATE TABLE dbo.yourTable
(
  SomeId  int identity NOT NULL,
  SomeCol varchar(8000),
  CONSTRAINT pk_yourTable PRIMARY KEY (SomeId)
);
GO

INSERT dbo.tally
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1
FROM sys.all_columns a, sys.all_columns b;

INSERT dbo.YourTable VALUES
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120');
GO

    2. dbo.tally 인덱싱 된 뷰를 생성하는 당신의 가치 밖으로 분할

    CREATE VIEW dbo.yourView
WITH SCHEMABINDING AS
SELECT 
 SomeId,
 SomeCol,
 position = N+1,
 Item =
 SUBSTRING
 (
   REPLACE(REPLACE(somecol, '-', '|'), ' ', '|'),
   N+1,
   ISNULL(NULLIF(CHARINDEX('|',REPLACE(REPLACE(somecol,'-','|'),' ','|'),N+1),0)-(N+1),8000)
 )
FROM dbo.YourTable
CROSS JOIN dbo.tally
WHERE N <= LEN(SomeCol) -- Use this predicate first to ensure we get a nonclustered index seek
AND (N=0 OR SUBSTRING(REPLACE(REPLACE(somecol,'-','|'),' ','|'),N,1) = '|');
GO

CREATE UNIQUE CLUSTERED INDEX uq_cl_yourView ON dbo.yourView(SomeId, position);
GO

    우리가 지금까지 무엇을하자 검토 :

    SELECT * FROM dbo.yourView;

    ;

    SomeId      SomeCol              position    item
----------- -------------------- ----------- -----
1           SC 5-1395-174-25P    1           SC
1           SC 5-1395-174-25P    4           5
1           SC 5-1395-174-25P    6           1395
1           SC 5-1395-174-25P    11          174
1           SC 5-1395-174-25P    15          25P
2           SC 1-2134-123-ABC C1 1           SC
2           SC 1-2134-123-ABC C1 4           1
2           SC 1-2134-123-ABC C1 6           2134
2           SC 1-2134-123-ABC C1 11          123
2           SC 1-2134-123-ABC C1 15          ABC
2           SC 1-2134-123-ABC C1 19          C1
2           SC 1-2134-123-ABC C1 22          2
3           SC 12-5245-1247-14&P 1           SC
3           SC 12-5245-1247-14&P 4           12
3           SC 12-5245-1247-14&P 7           5245
3           SC 12-5245-1247-14&P 12          1247
3           SC 12-5245-1247-14&P 17          14&P
4           SC ABCD-2525-120     1           SC
4           SC ABCD-2525-120     4           ABCD
4           SC ABCD-2525-120     9           2525
4           SC ABCD-2525-120     14          120

    3. "피벗"에 대한 제프 MODEN의 "크로스 탭"접근 방식

     WITH getItemNumber AS
    (
      SELECT 
        SomeId,
        ItemNumber = ROW_NUMBER() OVER (PARTITION BY SomeId ORDER BY position),
        Item 
      FROM dbo.yourView WITH (NOEXPAND)
    )
    SELECT 
      SomeId ,
      pos1 = MAX(CASE ItemNumber WHEN 1 THEN item END),
      pos2 = MAX(CASE ItemNumber WHEN 2 THEN item END),
      pos3 = MAX(CASE ItemNumber WHEN 3 THEN item END),
      pos4 = MAX(CASE ItemNumber WHEN 4 THEN item END),
      pos5 = MAX(CASE ItemNumber WHEN 5 THEN item END),
      pos6 = MAX(CASE ItemNumber WHEN 6 THEN item END), 
      pos7 = MAX(CASE ItemNumber WHEN 7 THEN item END)
    FROM getItemNumber
    GROUP BY SomeId;

    ** 결과 **

    SomeId  pos1    pos2   pos3    pos4  pos5   pos6   pos7
------- ------- ------ ------- ----- ------ ------ -----
1       SC      5      1395    174   25P    NULL   NULL
2       SC      1      2134    123   ABC    C1     2
3       SC      12     5245    1247  14&P   NULL   NULL
4       SC      ABCD   2525    120   NULL   NULL   NULL

    실행 계획을 검토하는 동안 미소 (4)

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

    4.그것의 시도

    그것의 시도

    Declare @YourTable table (ID int,SomeCol varchar(500))
Insert Into @YourTable values
(1,'5-1395-174-25P'),
(2,'1-2134-123-ABC C1-2'),
(3,'SC 12-5245-1247-14&P'),  
(4,'SC ABCD-2525-120*')

Select A.ID
      ,B.RetVal
 From  @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(replace(A.SomeCol,' ','§§Split§§'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) B
 Where RetSeq=1

    변경 필요

    5. from https://stackoverflow.com/questions/43789578/split-one-column-value-into-multiple-column-values by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버 ID 열 값은 0에서 시작하는 대신 1  (0) 2020.05.27
[SQL] dbms_utility.comma_to_table에 " 'XX.YY'무효 근처의 쉼표로 구분 된 목록"점점  (0) 2020.05.27
[SQL] 집계 (X, Y)에서의 PostgreSQL 점군 좌표  (0) 2020.05.27
[SQL] 위한 SQL 구문 용어 WHERE (COL1, COL2) <(VAL1, val2만큼) '  (0) 2020.05.27
[SQL] 포스트 그레스 테이블 컬럼의 디폴트 값을 얻을?  (0) 2020.05.27
,

티스토리툴바