CODEWITHSUNDEEP
sql-servert-sqlsql-update
Mark Kram
Mark Kram

Reputation: 5842

Concatenating Value in Update

Ok, I have been banging my head against the wall for about 20 minutes and I can't seem to figure this one out. I have two tables each with a common field (ID) and what I want to do is to concatenate the values form #T2's UDValue column into #T1's UDValue column

CREATE TABLE #T1(ID INT, UDValue NVARCHAR(50))
CREATE TABLE #T2(ID INT, UDValue NVARCHAR(50))

INSERT INTO #T1(ID)
    VALUES(1)

INSERT INTO #T2(ID, UDValue)
    VALUES(1, 'Tom')
          ,(1, 'Dick')
          ,(1, 'Harry')
          ,(2, 'Chevy')
          ,(3, 'Apple')
          ,(2, 'Ford')

UPDATE #T1
    SET UDValue = COALESCE(t1.UDValue, '') + t2.UDValue + ','
FROM
    #T1 AS t1
       INNER JOIN #T2 AS t2 ON t2.ID = t1.ID

SELECT * FROM #T1

DROP TABLE #T1
DROP TABLE #T2

So what I am looking for is to see my data like this:

ID  UDValue
1, Tom,Dick,Harry
2, Chevy,Ford
3, Apple

but this is what I am getting:

ID  UDValue
1   Tom,
2   Chevy,
3   Apple,

I want to avoid having to loop through each row but I don't see any alternatives.

Upvotes: 0

Views: 38

Answers (3)

Vadim Loboda
Vadim Loboda

Reputation: 3111

Concatenating Value in Update:

create table #T (Id int, Value varchar(50), primary key (Id, value));

declare @Id int;
declare @Value varchar(500);

insert into #T 
    (   Id  ,   Value   )
values
    (   1   ,   'Tom'   ),
    (   1   ,   'Dick'  ),
    (   1   ,   'Harry' ),
    (   2   ,   'Chevy' ),
    (   3   ,   'Apple' ),
    (   2   ,   'Ford'  );

update #T set    
   @Value = case when @Id is null or @Id = Id then @Value else null end,
   @Value = Value = coalesce(@Value + ', ', '') + Value,
   @Id = Id;

select Id, max(Value) from #T group by Id;

drop table #T;

The example works only if "primary key" is defined on the table.

More about "Quirky Update" is in Solving the Running Total and Ordinal Rank Problems

Upvotes: 1

radar
radar

Reputation: 13425

you can use stuff with for xml path to concatenate column values you can use a corelated sub query to get the comma separated values

Also it is not a good idea to store it as comma separated values in the database.

;with cte
as
(
select ID,         
         stuff((select  ','+ T2.UDValue 
         from #T2 T2
         where T2.ID = T1.ID
         FOR XML PATH ('')), 1,1,'') as NewValue
from #T1 T1
)
update #T1 
set UDValue = cte.NewValue
from cte
join #T1 
on cte.ID = #T1.ID

select * from #T1

Upvotes: 2

Mark Kram
Mark Kram

Reputation: 5842

This is what I have come up with so far but I am not sure that it is the most efficient way to do this:

CREATE TABLE #T1(ID INT, UDValue NVARCHAR(50))
CREATE TABLE #T2(ID INT, UDValue NVARCHAR(50))

INSERT INTO #T1(ID)
    VALUES(1)
          ,(2)
          ,(3)

    INSERT INTO #T2(ID, UDValue)
        VALUES(1, 'Tom')
              ,(1, 'Dick')
              ,(1, 'Harry')
              ,(2, 'Chevy')
              ,(3, 'Apple')
              ,(2, 'Ford')

DECLARE @id INT = 1, @UDValue NVARCHAR(MAX)

WHILE(@ID < 4)
    BEGIN
       SELECT @UDValue = STUFF((SELECT DISTINCT N',' + UDValue
                          FROM
                             #T2
                          WHERE ID = @ID
                        ORDER BY N',' + UDValue
                        FOR XML PATH(''), TYPE
                          ).value(N'.[1]',N'nvarchar(max)'),1,1,'');

       UPDATE #T1
          SET UDValue = @UDValue
       FROM
          #T1 AS t1
       WHERE
          t1.ID = @ID

       SELECT @ID += 1
    END

SELECT * FROM #T1

DROP TABLE #T1
DROP TABLE #T2

Upvotes: 0

Related Questions