복붙노트

[SQL] SQL Server 2008의 분할 기능

SQL

SQL Server 2008의 분할 기능

이 같은 열이 표를 가지고 :

+--+------+
|ID|Name  |
+--+------+
|1 |MSSQL |
+--+------+
|2 |MySQl |
+--+------+
|3 |Oracle|
+--+------+

표 2에서 나는 열 등이있다

+------------+
|Databasename|
+------------+
|1,3         |
+------------+
|2           |
+------------+
|1,2         |
+------------+

내 출력해야한다 :

+------------+
|Databasename|
+------------+
|MSSQL,Oracle|
+------------+
|MySQL       |
+------------+
|MSSQL,MYSQL |
+------------+

나는이 얻는 방법을,이 쿼리 필요 ..

해결법

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

    1.먼저, 가장 좋은 방법은 데이터베이스의 쉼표로 구분 된 목록에 데이터를 저장하지 않는 것입니다. 당신은 테이블 구조를 고정하는 것이 좋습니다.

    먼저, 가장 좋은 방법은 데이터베이스의 쉼표로 구분 된 목록에 데이터를 저장하지 않는 것입니다. 당신은 테이블 구조를 고정하는 것이 좋습니다.

    당신은 테이블 구조를 변경할 수없는 경우에, 당신은 올바른 이름을 지정하는 행 목록에서 데이터를 분할해야합니다. 데이터가 분할되면 당신은 목록에 데이터 등을 연결할 수 있습니다.

    이 당신이 온라인으로 찾을 수 많은 다른 분할 기능이 있지만 여기에 내가 일반적으로 사용하는 버전입니다 :

    CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

    나는 각 행과 관련된 고유 키가 표시되지 않기 때문에 당신의 결과를 얻으려면, 내가 분할 기능과 ROW_NUMBER ()를 적용하여 시작합니다. 각 행의 고유 키가 있다면 당신은)합니다 (ROW_NUMBER 필요하지 않습니다 :

    ;with cte as
(
  select rn, name, id
  from
  (
    select row_number() over(order by (select 1)) rn,
      databasename
    from table2
  ) t2
  cross apply dbo.split(t2.databasename, ',') i
  inner join table1 t1
    on i.items = t1.id
) 
select *
from cte

    이 쿼리는 다음에 당신의 쉼표로 구분 된 목록을 나누기 :

    | RN |   NAME | ID |
--------------------
|  1 |  MSSQL |  1 |
|  1 | Oracle |  3 |
|  2 |  MySQl |  2 |
|  3 |  MSSQL |  1 |
|  3 |  MySQl |  2 |

    올바른 이름을 가진 여러 행의 데이터가 있으면, 당신은리스트에 연결하는 다도 () 및 XML 경로를 사용할 수 있습니다. 당신은 전체 쿼리는 다음과 유사 할 것입니다 :

    ;with cte as
(
  select rn, name, id
  from
  (
    select row_number() over(order by (select 1)) rn,
      databasename
    from table2
  ) t2
  cross apply dbo.split(t2.databasename, ',') i
  inner join table1 t1
    on i.items = t1.id
) 
select  
  STUFF(
         (SELECT ', ' + c2.name
          FROM cte c2
          where c1.rn = c2.rn
          order by c2.id
          FOR XML PATH (''))
          , 1, 1, '') Databasename
from cte c1
group by c1.rn
order by c1.rn;

    데모와 SQL 바이올린을 참조하십시오.

    전체 쿼리의 결과는 다음과 같습니다

    |   DATABASENAME |
------------------
|  MSSQL, Oracle |
|          MySQl |
|   MSSQL, MySQl |
  2. ==============================

    2.당신은 분할 기능을 요구하고 있지만, 당신은 당신이 원하는 결과를 얻을 수 있도록 값을 분리 할 필요가 없습니다.

    당신은 분할 기능을 요구하고 있지만, 당신은 당신이 원하는 결과를 얻을 수 있도록 값을 분리 할 필요가 없습니다.

    이 쿼리는 쉼표 CONCATENATE 값으로 XML 트릭의를 사용하여 상관 하위 쿼리에 이름 목록을 분리 구축합니다. 이 표 2의 각 행에 대해 표 1에서 사용할 값을 알아낼처럼 사용합니다.

    select (
       select ', '+T1.Name
       from Table1 as T1
       where ','+T2.Databasename+',' like '%,'+cast(T1.ID as varchar(10))+',%'
       for xml path(''), type
       ).value('substring(text()[1], 3)', 'varchar(max)') as Databasenames
from Table2 as T2

    SQL 바이올린

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

    3.어떤 분할도없이 XML 경로하지만, 올바른 결과를 얻을 수 없습니다.

    어떤 분할도없이 XML 경로하지만, 올바른 결과를 얻을 수 없습니다.

    ;with cte as (
    select *, cast(null as varchar(1024)) as str, cast(0 as int) as ID
    from Table2

    union all

    select DatabaseName, (case when DatabaseName like cast(t.ID as varchar(32)) + ',%' 
                                    or DatabaseName like '%,' + cast(t.ID as varchar(32)) + ',%'
                                    or DatabaseName like '%,' + cast(t.ID as varchar(32)) 
                                    or DatabaseName = cast(t.ID as varchar(32)) then cast(isnull(str, '') + ',' + t.Name as varchar(1024)) else str end), cte.ID + 1 as ID
    from cte
    inner join Table1 t on cte.ID + 1 = t.ID
)
select DatabaseName, (case when str like ',%' then substring(str, 2, len(str)) else null end) as str
from cte c
where ID = (select max(ID) from cte where DatabaseName = c.DatabaseName)
  4. ==============================

    4.

    --Here it goes:

----------------
-- FieldCount --
----------------
CREATE FUNCTION [dbo].[FieldCount](@S VARCHAR(8000), @Separator VARCHAR(10))
  RETURNS INT
AS

BEGIN

  /*
  @Author: Leonardo Augusto Rezende Santos
  @Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
  */

  DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Result INT

  IF @Separator = ' ' 
    BEGIN
      SET @S = REPLACE(@S, ' ', '|-|')
      SET @Separator = '|-|'
    END

  WHILE CHARINDEX(@Separator + @Separator, @S) > 0
    SET @S = Replace(@S, @Separator + @Separator, @Separator + '_-_' + @Separator)
  IF @S <> ''
    SET @Result = 1
  ELSE
    BEGIN
      SET @Result = 0
      RETURN(@Result)
    END
  SET @Ptr = 0
  SET @LenS = LEN(@S)
  SET @LenSep = LEN(@Separator)
  SET @p = CHARINDEX(@Separator, @S)
  WHILE @p > 0
    BEGIN
      SET @Result = @Result + 1
      SET @Ptr = @Ptr + @p + @LenSep
      SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
    END

  RETURN(@Result)

END

--------------
-- GetField --
--------------
CREATE FUNCTION [dbo].[GetField](@S VARCHAR(8000), @Separator VARCHAR(10), @Field INT)
  RETURNS VARCHAR(8000)
AS

BEGIN

  /*
  @Author: Leonardo Augusto Rezende Santos
  @Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
  */

  DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Fld INT, @Result VARCHAR(8000)

  IF @Separator = ' ' 
    BEGIN
      SET @S = REPLACE(@S, ' ', '|-|')
      SET @Separator = '|-|'
    END

  IF @Field > dbo.FieldCount(@S, @Separator)
    BEGIN
      SET @Result = ''
      RETURN(@Result)
    END
  SET @Fld = 1
  SET @Ptr = 1
  SET @LenS = LEN(@S)
  SET @LenSep = LEN(@Separator)
  SET @p = CHARINDEX(@Separator, @S)
  WHILE (@p > 0) and (@Fld < @Field)
    BEGIN
      SET @Fld = @Fld + 1
      SET @Ptr = @Ptr + @p + @LenSep - 1
      SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
    END
  IF (@p = 0) and (@Fld = @Field)
    SET @p = @LenS - @Ptr + 2
  SET @Result = SUBSTRING(@S, @Ptr, @p - 1)

  RETURN(@Result)

END

/* USAGE*/

select dbo.FieldCount('A1 A2 A3 A4 A5', ' ')

--It will return 5

select dbo.GetField('A1 A2 A3 A4 A5', ' ', 3)

--It will return 'A3'

select dbo.GetField('A1/A2/A3/A4/A5', '/', 3)

--It will return 'A3'

--Hope it works for you.

--Leonardo Augusto

    5. from https://stackoverflow.com/questions/14911167/split-function-in-sql-server-2008 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL을 건너 뛰는 첫 번째 행  (0) 2020.04.27
[SQL] 그것은 (SQL Server 내) 트랜잭션 내부의 여러 DDL 문을 실행할 수 있습니까?  (0) 2020.04.27
[SQL] 타이 T-SQL에 대한 MySQL의 대안  (0) 2020.04.27
[SQL] T-SQL에서 피벗 데이터  (0) 2020.04.27
[SQL] 어떻게 Google BigQuery에서 범주의 수천 더미 변수 열을 만드는 방법?  (0) 2020.04.27
,

티스토리툴바