1

I have a table below

Program          FileCount
B1               1
A1;B2            2
A2;B3            1
A3;C1;B4         1
A3;C2;D1;B5;B6   3
C3;D2;B7         1
B8;B9            2
B8;B9            2

I'm only interested in program B and I would like to take all program B 

Program    FileCount
B1         1
B2         2  
B3         1  
B4         1
B5         3
B6         3
B7         1
B8         4
B9         4

Please note :

  1. I'm using Microsoft SQL Server 2012
  2. the name for program B are not uniform having 2 digits only but vary (example, B1233456, B123)

I have used the syntax below however it doesn't give what I need. 

SELECT

distinct CASE WHEN PATINDEX('%B%', Program)>0 THEN SUBSTRING(Program, PATINDEX('%B%', Program), 50) ELSE '' END as Program, 

sum(cast (Filecount as integer)) as FileCount

FROM dbo.Database1
where Program like '%B%'
group by Program

Thank you very much guys for your help.

Kind regards,

Adhi

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Adhitya Sanusi
  • 119
  • 2
  • 17
  • Possible duplicate of [Delimited Function in SQL to Split Data between semi-colon](https://stackoverflow.com/questions/45853527/delimited-function-in-sql-to-split-data-between-semi-colon) – Michał Turczyn Aug 25 '17 at 05:25
  • it is not a duplicate as this has more complicated issue regarding accumulated value of file count. – Adhitya Sanusi Aug 25 '17 at 05:34

2 Answers2

1

Here you go. Sql server 2016 has a new split function to use where I use ssf_split. my ssf_split function returns a table with one column named Textline.

I hope that is clear enough it's getting late :)

declare @t table (Program Varchar(max), Filecount integer)

insert into @t(Program ,   FileCount)
      select 'B1            ',   1
union all select 'A1;B2         ',   2
union all select 'A2;B3         ',   1
union all select 'A3;C1;B4      ',   1
union all select 'A3;C2;D1;B5;B6',   3
union all select 'C3;D2;B7      ',   1
union all select 'B8;B9         ',   2
union all select 'B8;B9         ',   2


select X.textline as Program, sum(t.filecount) as FileCount from @t t
Cross apply dbo.ssf_split(t.program,';') x where SUBSTRING(x.TextLine, 1,1) = 'b'
Group by x.TextLine
order by x.textline

will produce this output:

Program                FileCount
---------------------- -----------
B1                     1
B2                     2
B3                     1
B4                     1
B5                     3
B6                     3
B7                     1
B8                     4
B9                     4

So the 2016 function is STRING_SPLIT ( string , separator ) and the column name is Value.

so replace dbo.ssf_split with STRING_SPLIT and textline with value and you should be good to go.

Jay Wheeler
  • 379
  • 2
  • 7
1

First create a custom split function

        CREATE FUNCTION [dbo].[Split]
        (
            @String NVARCHAR(4000),
            @Delimiter NCHAR(1)
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
            WITH Split(stpos,endpos) 
            AS(
                SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
                UNION ALL
                SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
                    FROM Split
                    WHERE endpos > 0
            )
            SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
                'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
            FROM Split
        )

Than you can use this query to make the selection:

    declare @t table (Program Varchar(max), Filecount integer)

    insert into @t(Program ,   FileCount)
    select 'B1 ',  1
    union all select 'A1;B2', 2
    union all select 'A2;B3', 1
    union all select 'A3;C1;B4', 1
    union all select 'A3;C2;D1;B5;B6', 3
    union all select 'C3;D2;B7', 1
    union all select 'B8;B9', 2
    union all select 'B81;B9', 2


    SELECT DISTINCT s.Data, t.Filecount
    FROM @t t
    CROSS APPLY dbo.Split(t.Program, ';') s
    WHERE Data like 'B%'
Egbert
  • 158
  • 4
  • 12