[SQL] SQL 쉼표로 구분 컬럼 => 행에 다음 합계 합계?

SQL 쉼표로 구분 컬럼 => 행에 다음 합계 합계?

나는 2005 MS SQL을 사용하고 있습니다 나는 현재 내가 해결책을 얻기 위해 싸우고하고 문제를 가지고있다.

나는이 열이있는 테이블이 있습니다은 NameList, 시간

이름 목록 열은 데이터를 쉼표로 구분하고있다. 테이블 데이터는 다음과 같은 것이다 :

Namelist    Time
John Smith, Jeremy Boyle, Robert Brits, George Aldrich  5
John Smith, Peter Hanson    15
Jeremy Boyle, Robert Brits  10
....

나는이 최종 결과로 날을 제공 할 것입니다 SQL 표현의 일종이 필요합니다 :

Name    Total_Time
John Smith  20
Jeremy Boyle    15
Robert Brits    15

기타...... 기본적 표현은 모두에게 행과 수학 다른 행에있는 이름과 그 이름에 이름을 발견해야하고 각 사용자에 대해 함께 시간을 추가합니다.

내가 가진 아이디어는 어떻게 든 그것을위한 시간이 곱셈 ... 다음 무엇인지 행에 쉼표가 데이터를 구분 변환하고 각각의 고유 한 기록을 계산하는 것입니다 .....하지만 난 그것을 구현하는 방법에 대한 아무 생각이 없다

어떤 도움이 많이 주시면 감사하겠습니다

감사,

해결법

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

   1.나는 SQL에서 문자열을 분할 할 수 테이블 접근 방식을 선호

   나는 SQL에서 문자열을 분할 할 수 테이블 접근 방식을 선호

   작업이 방법, 당신이 한 번 테이블 설정을 수행해야합니다

   SELECT TOP 10000 IDENTITY(int,1,1) AS Number
       INTO Numbers
       FROM sys.objects s1
       CROSS JOIN sys.objects s2
   ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
   

   [숫자 테이블이 설정되면,이 분할 함수를 만들 :

   CREATE FUNCTION [dbo].[FN_ListToTable]
   (
        @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
       ,@List     varchar(8000)--REQUIRED, the list to split apart
   )
   RETURNS TABLE
   AS
   RETURN 
   (
   
       ----------------
       --SINGLE QUERY-- --this will not return empty rows
       ----------------
       SELECT
           ListValue
           FROM (SELECT
                     LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                     FROM (
                              SELECT @SplitOn + @List + @SplitOn AS List2
                          ) AS dt
                         INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                     WHERE SUBSTRING(List2, number, 1) = @SplitOn
                ) dt2
           WHERE ListValue IS NOT NULL AND ListValue!=''
   
   );
   GO 
   

   이제 쉽게 테이블에 CSV 문자열을 분할과에 가입 할 수 있습니다 :

   select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')
   

   산출:

   ListValue
   -----------------------
   1
   2
   3
   4
   5
   6777
   
   (6 row(s) affected)
   

   귀하의 캔은 지금과 같은 테이블의 모든 행을 분할하는 CROSS 적용 사용

   DECLARE @YourTable table (NameList varchar(5000), TimeOf int)
   INSERT INTO @YourTable VALUES ('John Smith, Jeremy Boyle, Robert Brits, George Aldrich',  5)
   INSERT INTO @YourTable VALUES ('John Smith, Peter Hanson',    15)
   INSERT INTO @YourTable VALUES ('Jeremy Boyle, Robert Brits',  10)
   
   SELECT
       st.ListValue AS NameOf, SUM(o.TimeOf) AS TimeOf
       FROM @YourTable  o
           CROSS APPLY  dbo.FN_ListToTable(',',o.NameList) AS st
       GROUP BY st.ListValue
       ORDER BY st.ListValue
   

   산출:

   NameOf                  TimeOf     
   ----------------------- -----------
   George Aldrich          5          
   Jeremy Boyle            15         
   John Smith              20         
   Peter Hanson            15         
   Robert Brits            15         
   
   (5 row(s) affected)
   

   이것을 사용하여, 나는 당신이 당신의 테이블 디자인을 변경하고 새로운 테이블에 INSERT이 출력을 사용하는 것이 좋습니다 것입니다. 즉 더 정규화 방법이 될 것입니다. 또한 그것이 번거 로움하게, 열 이름을 예약어를 사용하지 마십시오. 공지 사항 나는, "NameOf"와 "TimeOf"를 사용하여 어떻게 예약 된 단어를 사용하지 않도록.

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

   2.다음 중 하나를 다른 답변 천천히, 반복, 실시간으로 데이터를 수정하는 검색

   다음 중 하나를 다른 답변 천천히, 반복, 실시간으로 데이터를 수정하는 검색

   또는 : 표준화. 왜 정상화 그것에 대해에 왜 사람들 강타 존재 생각하십니까?

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

   3.당신은 많은 행에 이름 목록을 분할 테이블 반환 함수를 만들 수 있습니다 :

   당신은 많은 행에 이름 목록을 분할 테이블 반환 함수를 만들 수 있습니다 :

   if object_id('dbo.fnSplitNamelist') is not null
       drop function dbo.fnSplitNamelist
   go
   create function dbo.fnSplitNamelist(
       @namelist varchar(max))
   returns @names table (
       name varchar(50))
   as 
       begin
       declare @start int
       declare @end int
       set @start = 0
       while IsNull(@end,0) <> len(@namelist) + 1
           begin
           set @end = charindex(',', @namelist, @start)
           if @end = 0
               set @end = len(@namelist) + 1
   
           insert into @names select ltrim(rtrim(
               substring(@namelist,@start,@end-@start)))
   
           set @start = @end + 1
           end
       return
       end
   go
   

   당신은 십자가가 각각 이름 목록의 이름을 반환 신청을 할 수 있습니다. 그런 다음 사용자 당 시간 합계를 그룹으로 사용할 수 있습니다 :

   declare @YourTable table (namelist varchar(1000), time int)
   
   insert into @YourTable
   select 'John Smith, Jeremy Boyle, Robert Brits, George Aldrich',  5
   union all select 'John Smith, Peter Hanson',  15
   union all select 'Jeremy Boyle, Robert Brits',  10
   
   select fn.name, sum(t.time)
   from @YourTable t
   cross apply fnSplitNamelist(t.namelist) fn
   group by fn.name
   

   이 결과 :

   George Aldrich      5
   Jeremy Boyle        15
   John Smith          20
   Peter Hanson        15
   Robert Brits        15
   

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

   4.가장 좋은 방법은 데이터를 정상화하는 것입니다. 그런 다음에 작업을 훨씬 쉽게 될 것입니다.

   가장 좋은 방법은 데이터를 정상화하는 것입니다. 그런 다음에 작업을 훨씬 쉽게 될 것입니다.

   두 번째 최선의 선택은 각각의 이름 목록에서 한 번에 이름을 선택하는 재귀 쿼리를 사용하여 별도의 이름의 목록과 각 레코드에서 자신의 respecive 시간으로 돌아가, 각 이름에 대한 시간 합계를 그룹화 사용하는 것입니다.

   사용자 정의 함수 또는 사전 생성 된 테이블에 대한 필요가 없습니다. ;)

   with NameTime ([Name], [Time], Namelist)
   as (
     select cast(null as varchar(100)), [Time], Namelist
     from NamelistTime
     union all
     select
       case when Pos = 0 then NameList else substring(Namelist, 1, Pos - 1) end,
       [Time],
       case when Pos = 0 then null else substring(NameList, Pos + 2, len(Namelist) - Pos - 1) end
     from (
       select [Time], Namelist, Pos = charindex(', ', Namelist)
       from NameTime
     ) x
     where Namelist is not null
   )
   select [Name], sum([Time])
   from NameTime
   where [Name] is not null
   group by [Name]
   

   반대로, 정규화 데이터 작업, 이는 단순하게 될 것이다 :

   select p.Name, sum(n.Time)
   from NamelistTime n
   inner join Person p on p.PersonId = n.PersonId
   group by p.Name
   

from https://stackoverflow.com/questions/2341374/sql-comma-delimted-column-to-rows-then-sum-totals by cc-by-sa and MIT license