Barry Cunney
asked on
Repeat Primary Table field for every record returned by Table Valued function
Hi
I have the following parts data in a table in SQL Server
OEM_PartNumber Device_List
001R00588 | WCP7132 | WorkCentre 7132 | WCP7132 | WorkCentre 7132 |
001R00593 | WorkCentre 7232 | WorkCentre 7242 | WorkCentre 7232 |
I have a split string function and if I pass in the string from the Device List field above
select * from dbo.fn_SplitString('|', '| WCP7132 | WorkCentre 7132 | WCP7132 | WorkCentre 7132')
I get the records returned as follows
1 WCP7132
2 Xerox WorkCentre 7132
3 Xerox WCP7132
4 WorkCentre 7132
i.e a record for every string delimited by the |
using this function(maybe I need to modify the function) or some other trick(maybe CTE) I want to achive the the data in the following format for every record in the parts table
001R00588 WCP7132
001R00588 WorkCentre 7132
001R00588 WCP7132
001R00588 WorkCentre 7132
001R00593 WorkCentre 7232
001R00593 WorkCentre 7232
001R00593 WorkCentre 7232P
001R00593 WorkCentre 7242
001R00593 WorkCentre Pro 7232
001R00593 WorkCentre 7232
so I get a record for each string delimited by the pipe in the parts table, for every record in the parts table and the relevant part number beside it
This is the code for the split string function
ALTER FUNCTION [dbo].[fn_SplitString](@se p char(1), @s varchar(8000))
RETURNS TABLE
AS
RETURN
(
WITH Pieces(pn, start, stop)
AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s
FROM Pieces
)
I have the following parts data in a table in SQL Server
OEM_PartNumber Device_List
001R00588 | WCP7132 | WorkCentre 7132 | WCP7132 | WorkCentre 7132 |
001R00593 | WorkCentre 7232 | WorkCentre 7242 | WorkCentre 7232 |
I have a split string function and if I pass in the string from the Device List field above
select * from dbo.fn_SplitString('|', '| WCP7132 | WorkCentre 7132 | WCP7132 | WorkCentre 7132')
I get the records returned as follows
1 WCP7132
2 Xerox WorkCentre 7132
3 Xerox WCP7132
4 WorkCentre 7132
i.e a record for every string delimited by the |
using this function(maybe I need to modify the function) or some other trick(maybe CTE) I want to achive the the data in the following format for every record in the parts table
001R00588 WCP7132
001R00588 WorkCentre 7132
001R00588 WCP7132
001R00588 WorkCentre 7132
001R00593 WorkCentre 7232
001R00593 WorkCentre 7232
001R00593 WorkCentre 7232P
001R00593 WorkCentre 7242
001R00593 WorkCentre Pro 7232
001R00593 WorkCentre 7232
so I get a record for each string delimited by the pipe in the parts table, for every record in the parts table and the relevant part number beside it
This is the code for the split string function
ALTER FUNCTION [dbo].[fn_SplitString](@se
RETURNS TABLE
AS
RETURN
(
WITH Pieces(pn, start, stop)
AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s
FROM Pieces
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER