Want a Stored Procedure for Comma Seperated String Which Is of a Column (Has 20000 Rows ) in a Table

Want a stored procedure for comma seperated string which is of a column (has 20000 rows ) in a table

A Cross Apply with a a Parse should do the trick

Option 1 - With a UDF

Select A.ID
      ,B.* 
  From Campaign A
  Cross Apply [dbo].[tvf-Str-Parse-8K](A.Misc,',') B

Option 2 - Without a UDF

Select A.ID
      ,B.* 
  From Campaign 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(A.Misc,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
              ) B

Both Return

ID  RetSeq RetVal
1   1       h101
1   2       h202
1   3       h506
1   4       h707
1   5       h112
1   6       h566
2   1       h101
2   2       h102
2   3       h508
...
4   3       h432
4   4       h456
4   5       h678
4   6       h101
4   7       h807

The UDF if Needed

CREATE FUNCTION [dbo].[tvf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
--Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)

Sending the stored procedure output as comma-separated rather than multiple rows

It can be done by using a cursor

declare @Travel nvarchar(max)
declare @ID int
declare @Country nvarchar(max)
declare @Dept nvarchar(max)
declare @Role nvarchar(max)

declare @string nvarchar(max)

declare cursor1 cursor for
     SELECT  ID, Dept, Role, Country 
     FROM TravelManager

set @string = '';

open cursor1

fetch next from cursor1 into  @ID, @Dept, @Role, @Country

while (@@Fetch_Status <> -1)    
begin
    set @string += convert(varchar(10), @ID) + ',' +
                   @Dept + ',' + @Role + ',' + @Country + char(13) // char(13) for new line
    fetch next from cursor1 into  @ID, @Dept, @Role, @Country
end

close cursor1
deallocate cursor1

select  @string as ReturnValue

char(13) adds the carriage return else you can add \n I guess

Edit: removed selecting Travel

Read comma separated string in Sql server in loop

Try this article uses function to read the comma separated values and return the table

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
--     ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

just call the function from stored procedure or just call function like below:

SELECT items FROM [dbo].[Split] ('20030,20031,20033,20034,20065,20045,20044', ',') 

How to combine 2 or more rows in one and concatenate one column with comma Seperator

        CREATE VIEW  [dbo].[ret_vwSalariedForReport]
AS
     WITH temp1 AS (SELECT
     salaried.*,
     operationalUnits.Title as OperationalUnitTitle
FROM
    ret_vwSalaried salaried LEFT JOIN
    prs_operationalUnitFeatures operationalUnitFeatures on salaried.[Guid] = operationalUnitFeatures.[FeatureGuid] LEFT JOIN 
    prs_operationalUnits operationalUnits ON operationalUnits.id = operationalUnitFeatures.OperationalUnitID 
    ), 
temp2 AS (SELECT
    t2.*,
    STUFF ((SELECT ' - ' + t1.OperationalUnitTitle
        FROM
            temp1 t1 
        WHERE t1.[ID] = t2.[ID]  
        For XML PATH('')), 2, 2, '') OperationalUnitTitles from temp1 t2) 
SELECT 
    [Guid],
    ID,
    Title,
    PersonnelNo,
    FirstName,
    LastName,
    FullName,
    Active,
    SSN,
    DeathDate,
    SalariedType,
    OperationalUnitTitles
FROM 
    temp2
GROUP BY 
    [Guid],
    ID,
    Title,
    PersonnelNo,
    FirstName,
    LastName,
    FullName,
    Active,
    SSN,
    DeathDate,
    SalariedType,
    OperationalUnitTitles

---

Related Topics