April 18, 2018 at 4:57 am
Hi all,
We have data in this format (slightly simplified):
ID DataChanges ColumnChanges
1 Hi, ABC, 9812, TEST 123,1,44,23
2 AS,1244 12,133
3 BBC, dd 12,145
Here is a code to set up scenario:
create table #Data
(ID Int,
DataChanges varchar(100),
ColumnChanges varchar(100)
)
Insert into #Data
values (1 ,'Hi, ABC, 9812,TEST','123,1,44,23'),
(2,'AS,1244','12,133'),
(3,'BBC,dd','12,145')
and I want to try and translate it to this:
ID Datachanges ColumnChanges
1 Hi 123
1 ABC 1
1 9812 44
1 TEST 23
2 AS 12
2 1244 133
3 BBC 12
3 dd 145
So effectively the first item in Data to be along with the first item in column changes. I would rather be able to do this without a splitting function (as will need to request access to the server to add this).
Is it possible without a splitter?
Even if I did have a splitting function how do I ensure that both columns are split and end up together in the correct order.
Any help gratefully received.
Dan
April 18, 2018 at 6:35 am
danielfountain - Wednesday, April 18, 2018 4:57 AMHi all,We have data in this format (slightly simplified):
ID DataChanges ColumnChanges
1 Hi, ABC, 9812, TEST 123,1,44,23
2 AS,1244 12,133
3 BBC, dd 12,145Here is a code to set up scenario:
create table #Data
(ID Int,
DataChanges varchar(100),
ColumnChanges varchar(100)
)
Insert into #Data
values (1 ,'Hi, ABC, 9812,TEST','123,1,44,23'),
(2,'AS,1244','12,133'),
(3,'BBC,dd','12,145')
and I want to try and translate it to this:
ID Datachanges ColumnChanges
1 Hi 123
1 ABC 1
1 9812 44
1 TEST 23
2 AS 12
2 1244 133
3 BBC 12
3 dd 145So effectively the first item in Data to be along with the first item in column changes. I would rather be able to do this without a splitting function (as will need to request access to the server to add this).
Is it possible without a splitter?
Even if I did have a splitting function how do I ensure that both columns are split and end up together in the correct order.
Any help gratefully received.
Dan
I know you said you'd rather not use a splitter, but it really is the right tool for the job. This query uses Jeff Moden's splitter; it's in my util database and available to all users.
WITH cteData AS (
SELECT ID, DataChanges, ColumnChanges
FROM (VALUES(1, 'Hi, ABC, 9812, TEST', '123,1,44,23'),
(2, 'AS,1244', '12,133'),
(3, 'BBC, dd', '12,145')
) x (ID, DataChanges, ColumnChanges)
)
SELECT d.ID, s1.Item, s2.Item
FROM cteData d
CROSS APPLY util.dbo.DelimitedSplit8K(d.DataChanges, ',') s1
CROSS APPLY util.dbo.DelimitedSplit8K(d.ColumnChanges, ',') s2
WHERE s1.ItemNumber = s2.ItemNumber
ORDER BY d.ID, s1.ItemNumber;
You must have some access to the server to be able to run your query. If this is typical of the way your systems collect data, I think having a set-based splitter available is a must. It'll make things so much simpler for more queries than just this one. I'd suggest making the request to either be able to create it yourself or have it created for you.
The alternative is to use SQL to recreate the functionality of the splitter and query everything yourself, but it's better to go with proven code and use it whenever you need it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply