Hi All,
Not sure If I'm over complicating the process but I'm struggling to write or find a solution on line that works exactly how I expect to see the results.
I have found one solution but there are limitations on the PARSENAME when over 4 values.
Below is just copy pasted a solution from the web, I know this is overkill with the replace etc... I just haven't modified the code to clean that up.
I've tried alternatives using string split, which wasn't successful and the closest I can find it the above.
Below is the sample code I'm trying to work with. The string I have has a lot more | values than 4-5 but I guess that shouldn't make a difference if I can get 5-6 values working (unless there is a limitation over X amount like PARSENAME has)
I'm thinking CHARINDEX is the way to go, but I can't get this working.
Any help would be appreciated.
-- WORKING EXAMPLE
DROP TABLE IF exists #PipeStringTemp
CREATE TABLE #PipeStringTemp(PipeValues VARCHAR(1000));
INSERT INTO #PipeStringTemp(PipeValues)
VALUES ('11/10/2023|5|5|5')
SELECT
REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 1)) AS ColA
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 2)) AS ColB
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 3)) AS ColC
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 4)) AS ColD
FROM
#PipeStringTemp;
GO
-- LIMITATION OF PARSENAME
DROP TABLE IF exists #PipeStringTemp2
CREATE TABLE #PipeStringTemp2(PipeValues VARCHAR(1000));
INSERT INTO #PipeStringTemp2(PipeValues)
VALUES ('11/10/2023|5|5|5|2|1')
SELECT
REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 1)) AS ColA
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 2)) AS ColB
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 3)) AS ColC
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 4)) AS ColD
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 5)) AS ColE
,REVERSE(PARSENAME(REPLACE(REVERSE(PipeValues), '|', '.'), 6)) AS ColF
FROM
#PipeStringTemp2;
October 11, 2023 at 8:28 am
PARSENAME won't work for more than 4 "parts".
and instead of trying to invent the wheel again look at already made functions
https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 -- use this one as it is faster than the original on SQL Versions that allow for LEAD window function
and its original https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
read both as they are rather interesting reads.
from your use of "reverse" it seems that you wish for the elements to be returned in reverse order - why?
and if this is the case how does it work for rows with different number of elements - where the "date" won't always be on the same output column.
October 11, 2023 at 9:09 am
from your use of "reverse" it seems that you wish for the elements to be returned in reverse order - why?
and if this is the case how does it work for rows with different number of elements - where the "date" won't always be on the same output column.
In terms of the reverse, there is no requirement to it, as mentioned it was just code taken online. Just to show what I'm trying to achieve with the output. Probably confused the situation by having it in.
The good news is, it's a fixed element per type coming from the Application. for one of my types e.g. Customers there will be a FirstName, LastName, Phone, DOB. If details are entered or not, it will always be John|||2023-10-11
So I can safely assume as this is a one of export of Data, If the app Changes then correct, I will need to make that change but its very unlikely.
I'll review those articles as I got an example working on 1 row but when there is multiple rows Its not.
October 11, 2023 at 10:55 am
I'm still struggling to adapt the articles based on the examples.
Maybe I simplified it easier on the previous example.
I've mocked up a more "realistic" data set but still cannot get it as expected.
DROP TABLE IF EXISTS #data
CREATE TABLE #data (ResourceType VARCHAR(255), CustomerID INT, DOB DATE, details VARCHAR(1000))
INSERT INTO #data (ResourceType,CustomerID,DOB,details)
VALUES
('records',1,'2023-10-11','11/10/2023|5|5|5|2|TEST 123'),
('records',1,'2023-10-11','11/10/2023|2|1|1|1|'),
('records',1,'2023-10-11','11/10/2023|1|||3|')
SELECT * FROM #data
I was able to get it working using the below but I Need to keep putting a crazy amount of statements which will fail if result set keeps growing.
MAX(CASE WHEN RowNum = 1) THEN Value END
MAX(CASE WHEN RowNum = 2) THEN Value END
MAX(CASE WHEN RowNum = n. ..) THEN Value END
Do you have any further assistance.
October 11, 2023 at 12:13 pm
yes you will need to either change the code and have multiple conditions or use dynamic sql to build the output if your final output should have each "string" placed on its own column.
pretty much similar to what you were trying to do with parsename.
October 11, 2023 at 1:33 pm
Does this help? It relies on Jeff Moden's infamous splitter which, given the number of points you have, I imagine you've heard of.
DROP TABLE IF EXISTS #PipeStringTemp;
CREATE TABLE #PipeStringTemp
(
Id INT IDENTITY(1, 1)
,PipeValues VARCHAR(1000)
);
INSERT INTO #PipeStringTemp
(
PipeValues
)
VALUES
('11/10/2023|5|5|5|2|TEST 123')
,('11/10/2023|2|1|1|1|')
,('11/10/2023|1|||3|');
WITH detail
AS (SELECT pst.Id
,pst.PipeValues
,c1.ItemNumber
,c1.Item
FROM #PipeStringTemp pst
CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit8K (pst.PipeValues, '|') ) c1 )
SELECT detail.Id
,detail.PipeValues
,Somedate = MAX (IIF(detail.ItemNumber = 1, detail.Item, NULL))
,Int1 = MAX (IIF(detail.ItemNumber = 2, detail.Item, NULL))
,Int2 = MAX (IIF(detail.ItemNumber = 3, detail.Item, NULL))
--etc etc
FROM detail
GROUP BY detail.Id
,detail.PipeValues;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2023 at 1:57 pm
have a look at this example - based on something I do for dynamic reports
drop table if exists #data
CREATE TABLE #data
( RecordID int identity(1, 1) -- added as the use of pivot for this purposes requires a UNIQUE set of columns and sample data didn't contain it
, ResourceType VARCHAR(255)
, CustomerID INT
, DOB DATE
, details VARCHAR(1000)
)
insert into #data
( ResourceType
, CustomerID
, DOB
, details
)
values ('records', 1, '2023-10-11', '11/10/2023|5|5|5|2|TEST 123')
, ('records', 1, '2023-10-11', '11/10/2023|2|1|1|1|')
, ('records', 1, '2023-10-11', '11/10/2023|1|||3|')
drop table if exists #workdata;
select RecordID
, ResourceType
, CustomerID
, DOB
, 'FLD' + convert(varchar(10), ItemNumber) as FieldID -- add a prefix so we can have a "better" output column names
, Item
into #workdata
from #data dt
cross apply dbo.DelimitedSplit8K_LEAD(dt.details, '|')
select *
from #workdata
declare @execsql nvarchar(max)
declare @basesql nvarchar(max)
declare @pivcols nvarchar(max)
declare @pivsel nvarchar(max)
-- get list of columns to pivot as well as select list
-- optionally add a coalesce to replace nulls with spaces (or zeros/specific value if we know what a particular fieldid datatype and default value is)
select @pivcols = string_agg(convert(nvarchar(max), quotename(fieldID)), ',') within group (order by fieldid)
, @pivsel = string_agg(convert(nvarchar(max), ', piv.' + quotename(fieldID)) + ' as ' + quotename(fieldID), '') within group (order by fieldid)
from (select distinct fieldid
from #workdata
) t
set @basesql = '
select piv.RecordID
, piv.ResourceType
, piv.CustomerID
, piv.DOB
--pivsel
from ( -- subselect added so we can remove unwanted columns and to make block of code common to any further blocks required
select *
from #workdata
) src
PIVOT (max(src.item) for fieldid in
(
--pivcols
)
) piv
order by piv.RecordID
'
set @execsql = replace(replace(@basesql, '--pivsel', @pivsel), '--pivcols', @pivcols)
print @execsql
exec sys.sp_executesql @execsql
It's just another expression per value, not statement. You could pre-code 10 or even 20 values without a lot of trouble:
SELECT ResourceType, CustomerID, DOB,
ca1.*
FROM #data
CROSS APPLY (
SELECT
MAX(CASE WHEN ItemNumber = 1 THEN Item END) AS Value01,
MAX(CASE WHEN ItemNumber = 2 THEN Item END) AS Value02,
MAX(CASE WHEN ItemNumber = 3 THEN Item END) AS Value03,
MAX(CASE WHEN ItemNumber = 4 THEN Item END) AS Value04,
MAX(CASE WHEN ItemNumber = 5 THEN Item END) AS Value05,
MAX(CASE WHEN ItemNumber = 6 THEN Item END) AS Value06,
MAX(CASE WHEN ItemNumber = 7 THEN Item END) AS Value07,
MAX(CASE WHEN ItemNumber = 8 THEN Item END) AS Value08,
MAX(CASE WHEN ItemNumber = 9 THEN Item END) AS Value09,
MAX(CASE WHEN ItemNumber =10 THEN Item END) AS Value10 /*, ...*/
FROM (
SELECT *
FROM dbo.DelimitedSplit8K(details, '|') ds
) AS derived
) AS ca1
/*ORDER BY ...*/
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 17, 2023 at 6:49 am
Hi All,
Sorry for the delay. I was able to get this working using the help of the above. I did use a custom string split function which I found online.
I initially got it working using the STRING_SPLIT function, however i found out it didn't give me the correct ordinality, it kept changing which caused issues.
So using the custom string split scripts with a CROSS APPLY got it to work for me.
Since the values in Pipe delimiter were fixed per type, I used the MAX(CASE ...) option which worked.
The code was very similar to Scotts above.
Thanks for every ones help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply