Update all the values to one based on another column

Transact-SQL
1

Hi,

I have data like below

Product-----VendorID
ABC---------1234
ABC---------5678 | 1234
ABC--------6789
XYZ--------1234
XYZ--------6789

want to change in below format

Product-----VendorID
ABC--------1234 | 5678 |6789
ABC--------1234 | 5678 |6789
ABC--------1234 | 5678 |6789
XYZ--------1234 | 6789
XYZ--------1234 | 6789

Any suggestion on update statement will be very helpful

thanks

2

Welcome

Why are you opting for such a table design? Or is this for some reporting or some api called by another system?

3

yes, you are correct it is for reporting requirement.

4

Which version of SQL Server are you using? At least 2017 or something less than 2017?

5

thanks for your interest, SQL server 2008

6

There is a split function for 2008 you can use, then you can use stuff

DDL and Data

Summary

create table #Vendor (Product varchar(10),
VendorID varchar(20))

insert into #Vendor values
('ABC','1234')
,('ABC','5678 | 1234')
,('ABC','6789')
,('XYZ','1234')
,('XYZ','6789')

Split String Function

Summary

CREATE FUNCTION dbo.SplitString
(
@List nvarchar(max),
@Delim nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1
FROM n WHERE n <= LEN(@List))
SELECT [Value] = SUBSTRING(@List, n,
CHARINDEX(@Delim, @List + @Delim, n) - n)
FROM n WHERE n <= LEN(@List)
AND SUBSTRING(@Delim + @List, n, DATALENGTH(@Delim)/2) = @Delim
);
GO

Query

Summary

Select v.Product, x.Vendors
from #Vendor v
join (Select Product,
STUFF(
( Select distinct '|' + ltrim(rtrim([value]))
from #Vendor v1
cross apply dbo.SplitString(VendorID, '|')
where v1.Product = v.Product
FOR XML PATH('')
),
1,
1,''
) AS Vendors
FROM #Vendor V
GROUP BY Product) x
on x.Product = v.Product

7

in that case why is the existing data in this format?

Product-----VendorID
ABC---------1234
ABC---------5678 | 1234    <= is this the way you currently have it in the db?
8

Man... you really don't want to use that one. It relies on concatenation of delimiters and, because of that, has a horrible performance curve.

Use "DelimitedSplit8K" or "DelimitedSplitN4K" instead.

1 Like
9

Jeff, I knew you had one but couldn't remember what the name was. For anyone reading this, the link is below

1 Like
10

This table data is for reporting, then found the issue of vendorID not correct order is causing issue. so we wanted everything to same so it all be grouped together.

11

thank you very much, it is working. Now I need to change the code in way to work for the original table.

12

You can certainly use what you want but that split function will make sucking sounds for performance. I recommend you scroll up for the link to the DelimitedSplit8K function,

13

Sure, I will review the link. thanks for your support. Happy Holidays!!!