You must Sign In to post a response.
  • Category: SQL Server

    Update Comma separated id with there value

    Hi ,

    Our application have a table like below

    Table1

    HQID OK Fault
    1000 1,2,3 4
    1001 1,2 3,4
    ......... so on

    Table 2
    ID name
    1 screen
    2 button
    3 case
    4 power
    ...... so on

    [ use dummy value to present a how data save in DB.]
    [ may be DB design is not very good, but now we can't change its structure]

    For a procedure work we need data something like this

    Table3

    HQID OKString FaultString
    1000 screen,button,case power
    1001 screen,button case,power

    Do not want to use any UDF because we have a procedure in which we retrieve data as above form. we are already using UDF but as record grown up in Database, it slow down.

    Thanks in advance
  • #755815
    Hi Sandeep,

    As per your requirement I have created a procedure. Kindly have a look below .

    I will explain in brief ,

    Step 1 : I have create a table of name as Table1 with columns HQID,OK,Fault

    Step2 : I have create a table of name as Table2 with columns ID,name

    Step 3 : I have create a table of name as Table2 with columns HQID,OKString,FaultString.

    Step 4 : In procedure first I have taken the count of the table1 to use make the while loop execute.

    Step4 : Inside the while loop I have created another while loop which execute based on the count of the id available in the OK column for each HQID

    Step 5 : From the first inside while loop I get the OKDescription of each HQID.

    Step6 : Similarly like above in second inside while loop I get the FaultDescription of each HQID.

    Step 7 : Final I have inserted the data to table3 which I have got as an output for every loop.

    Below is the procedure I have use. Kindly have a look.




    Declare @CurrentCount int
    Set @CurrentCount=0

    Declare @LoopCount int

    Select @LoopCount=Count(*) from table1

    while(@CurrentCount < @LoopCount)
    BEGIN


    -- Start To Get the name OF OK Column Start ---

    Declare @CurrentCount1 int
    SET @CurrentCount1=0
    Declare @LoopCount1 int
    DECLARE @HQID int
    DECLARE @OkDescription varchar(Max)=null
    DECLARE @OKID int

    Select @HQID=HQID from (Select Row_Number() Over ( order by HQID ) AS RowNumber,HQID from table1) AS Temp where (RowNumber-1)=@CurrentCount



    SELECT @LoopCount1 = (LEN(OK) - LEN(REPLACE(OK,',','')) + 1) FROM table1 where HQID=@HQID

    while(@CurrentCount1 < @LoopCount1)
    BEGIN
    SELECT @OKID = PARSENAME(REPLACE(OK, ',', '.'), (@CurrentCount1+1)) from table1 where HQID=@HQID


    IF(@OkDescription<>'')
    BEGIN
    SET @OkDescription= @OkDescription+','+(Select Name from Table2 where ID=@OKID)

    END
    ELSE
    BEGIN
    Select @OkDescription = Name from Table2 where ID=@OKID

    END

    SET @CurrentCount1=@CurrentCount1+1
    END


    -- Start To Get the name OF OK Column END ---



    -- Start To Get the name OF OK Column Start ---

    Declare @CurrentCount2 int
    SET @CurrentCount2=0
    Declare @LoopCount2 int

    DECLARE @FaultDescription varchar(Max)=null
    DECLARE @FaultID int



    SELECT @LoopCount2 = (LEN(Fault) - LEN(REPLACE(Fault,',','')) + 1) FROM table1 where HQID=@HQID

    while(@CurrentCount2 < @LoopCount2)
    BEGIN
    SELECT @FaultID = PARSENAME(REPLACE(Fault, ',', '.'), (@CurrentCount2+1)) from table1 where HQID=@HQID


    IF(@FaultDescription<>'')
    BEGIN
    SET @FaultDescription= @FaultDescription+','+(Select Name from Table2 where ID=@FaultID)

    END
    ELSE
    BEGIN
    Select @FaultDescription = Name from Table2 where ID=@FaultID

    END

    SET @CurrentCount2=@CurrentCount2+1
    END


    -- Start To Get the name OF OK Column END ---


    Select @HQID
    Select @OkDescription
    Select @FaultDescription


    Insert into table3 values (@HQID,@OkDescription,@FaultDescription)


    SET @CurrentCount=@CurrentCount+1
    END




    I hope this will full fill your requirements. Kindly let me know your comments once you have used this code.

    Thanks,
    Bala

  • #755859
    Hi

    Use the below sql script to get your output

    create table table1(id int, value varchAR(50))
    INSERT INTO table1 values(1000,'1,2,3,4')
    INSERT INTO table1 values(1001,'1,2,3,4')
    select * from table1

    create table table2(id int, value varchAR(50))

    INSERT INTO table2 values(1,'screen')
    INSERT INTO table2 values(2,'button')
    INSERT INTO table2 values(3,'case')
    INSERT INTO table2 values(4,'power')


    exec sp_UpdateCommaseparated


    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE sp_UpdateCommaseparated
    AS
    BEGIN

    CREATE TABLE #temptable
    (
    id INT,
    value VARCHAR(50)
    )

    SET nocount ON

    DECLARE @Id INT
    DECLARE @InStr VARCHAR(50)
    DECLARE @SP INT
    DECLARE @VALUE VARCHAR(1000)
    DECLARE cur_emp CURSOR static FOR
    SELECT id,
    value
    FROM table1

    OPEN cur_emp

    IF @@CURSOR_ROWS > 0
    BEGIN
    FETCH next FROM cur_emp INTO @Id, @InStr

    WHILE @@Fetch_status = 0
    BEGIN
    PRINT 'ID : ' + CONVERT(VARCHAR(20), @Id)
    + ', Name : ' + @InStr + ', Salary : '
    + CONVERT(VARCHAR(20), @InStr)

    SET @InStr = Replace(@InStr + ',', ',,', ',')

    WHILE Patindex('%,%', @INSTR) <> 0
    BEGIN
    SELECT @SP = Patindex('%,%', @INSTR)

    SELECT @VALUE = LEFT(@INSTR, @SP - 1)

    SELECT @INSTR = Stuff(@INSTR, 1, @SP, '')

    INSERT INTO #temptable
    VALUES (@Id,
    @VALUE)
    END

    FETCH next FROM cur_emp INTO @Id, @InStr
    END
    END

    CLOSE cur_emp

    DEALLOCATE cur_emp

    SET nocount OFF

    SELECT t1.id,
    t2.value
    INTO #t
    FROM #temptable t1
    JOIN table2 t2
    ON t1.value = t2.id

    SELECT id,
    Stuff((SELECT ', ' + Cast(value AS VARCHAR(10)) [text()]
    FROM #t
    WHERE id = t.id
    FOR xml path(''), type) .value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')
    value
    FROM #t t
    GROUP BY id

    DROP TABLE #temptable

    DROP TABLE #t



    END
    GO

    Mark this answer if its ok for you


  • Sign In to post your comments