CODEWITHSUNDEEP
sql-servert-sql
Owain Esau
Owain Esau

Reputation: 1922

TSQL, change value on a comma delimited column

I have a column called empl_type_multi which is just a comma delimited column, each value is a link to another table called custom captions.

For instance, i might have the following as a value in empl_type_multi:

123, RHN, 458

Then in the custom_captions table these would be individual values:

123 = Dog
RHN = Cat
458 = Rabbit

All of these fields are NTEXT.

What i am trying to do is convert the empl_type_multi column and chance it to the respective names in the custom_captions table, so in the example above:

123, RHN, 458

Would become

Dog, Cat, Rabbit

Any help on this would be much appreciated.

----- EDIT ------------------------------------------------------------------

Ok so ive managed to convert the values to the corresponding caption and put it all into a temporary table, the following is the output from a CTE query on the table:

ID1     ID2     fName   lName   Caption_name        Row_Number
10007   22841   fname1  lname1  DENTAL ASSISTANT    1
10007   22841   fname1  lname1                      2
10007   22841   fname1  lname1                      3
10008   23079   fname2  lname2  OPS WARD            1
10008   23079   fname2  lname2  DENTAL              2
10008   23079   fname2  lname2                      3

How can i update this so that anything under caption name is added to the caption name of Row_Number 1 separated by a comma?

If i can do that all i need to do is delete all records where Row_Number != 1.

------ EDIT --------------------------------------------------

The solution to the first edit was:

WITH CTE AS 
(
    SELECT
        p.ID1
        , p.ID2
        , p.fname
        , p.lname
        , p.caption_name--
        , ROW_NUMBER() OVER (PARTITION BY p.id1ORDER BY caption_name DESC) AS RN
    FROM tmp_cs p
)
UPDATE tblPerson SET empType = empType + ', ' + c.Data
FROM CTE c WHERE [DB1].dbo.tblPerson.personID = c.personID AND RN = 2

And then i just incremented RN = 2 until i got 0 rows affected.

This was after i ran:

DELETE FROM CTE WHERE RN != 1 AND Caption_name = ''

Upvotes: 0

Views: 76

Answers (3)

Pream
Pream

Reputation: 537

You can construct the caption_name string easily by looping through while loop

declare @i int = 2,@Caption_name varchar(100)= (select series from 
#temp where Row_Number= 1)
while @i <= (select count(*) from #temp)
begin
select @Caption_name = @Caption_name + Caption_name from #temp where Row_Number = @i)
set @i = @i+1
end
update #temp set Caption_name = @Caption_name where Row_Number = 1

and use case statement to remove null values

(select case when isnull(Caption_name ,'') = '' then
'' else ',' + Caption_name end

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

you have just shared your part of problem,not exact problem.

try this,

DECLARE @T TABLE(ID1 VARCHAR(50),ID2 VARCHAR(50),fName VARCHAR(50),LName VARCHAR(50),Caption_name VARCHAR(50),Row_Number INT)
INSERT INTO @T VALUES
(10007,22841,'fname1','lname1','DENTAL ASSISTANT',    1)
,(10007,22841,'fname1','lname1',   NULL,                 2)
,(10007,22841,'fname1','lname1',   NULL,                 3)
,(10008,23079,'fname2','lname2','OPS WARD',            1)
,(10008,23079,'fname2','lname2','DENTAL',              2)
,(10008,23079,'fname2','lname2',  NULL,                  3)

SELECT * 
,STUFF((SELECT ','+Caption_name
FROM @T T1 WHERE T.ID1=T1.ID1 FOR XML PATH('')
),1,1,'')
FROM @T T

Upvotes: 1

MPR
MPR

Reputation: 91

select ID1, ID2, fname, lname, left(captions, len(captions) - 1) as captions
from (
    select distinct ID1, ID2, cast(fname as nvarchar) as fname, cast(lname as nvarchar) as lname, (
        select cast(t1.caption_name as nvarchar) + ',' 
        from #temp as t1
        where t1.ID1 = t2.ID1
        and t1.ID2 = t2.ID2
        and cast(caption_name as nvarchar) != ''
        order by t1.[row_number]
        for xml path ('')) captions
    from #temp as t2
) yay_concatenated_rows

This will give you what you want. You'll see casting from ntext to varchar. This is necessary for comparison because many logical ops can't be performed on ntext. It can be implicitly cast back the other way so no worries there. Note that when casting I did not specify length; this will default to 30, so adjust as varchar(length) as needed to avoid truncation. I also assumed that both ID1 and ID2 form a composite key (it appears this is so). Adjust the join as you need for the relationship.

Upvotes: 1

Related Questions