Deleted
-
Deleted.
-
Have a look into the appropriately called function STRING_SPLIT, or, if you need to keep detail on the ordinal position, DelimitedSplit8k. If you have any problems, please do reply with what you tried
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Deleted.
- chocthree - Monday, August 13, 2018 8:17 AM
You'll need to CROSS APPLY the STRING_SPLIT function as it is a table valued function, otherwise it will be parsed as a scalar object.
😎There are two variants of the DelimitedSplit8k function for SQL Server 2008 and earlier and for SQL Server 2012 and later respectfully which are alternatives to the STRING_SPLIT, most useful when you need to preserve the order of the elements, DelimitedSplit8k and DelimitedSplit8k_LEAD
- chocthree - Monday, August 13, 2018 8:17 AM
Otherwise, if you did use CROSS APPLY, and got this error, it means that you're not using the version of SQL Server you think you are. STRING_SPLIT was introduced in SQL Server 2016, so it is on 2017 (which is the forum you've psoted in), however, if you're using a version prior to that, then you'll get an error.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, August 13, 2018 9:20 AM
You should also get an error if your database is set at a lower compatibility level than 2016 ...
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.
-
STRING_SPLIT won't help in this case as it provides no guarantees of what order the results are in nor any way of detecting the position in the string that each element came from (something of a flaw in the current design). Get a copy of DelimitedSplit8K from this site (assuming your columns are never over 8000 characters) as that provides the requisite capabilities.
- chocthree - Monday, August 13, 2018 6:30 AMHi,
I have the following query:SELECT
rc.[EmailAddress] AS [Email Address]
, cd.[Name] AS [Candidates Last : Candidate First]
, CASE
WHEN CHARINDEX(':', cd.Name) <> 0 THEN
RIGHT( cd.Name, LEN( RIGHT( cd.Name, LEN( cd.Name ))) - LEN( RIGHT( cd.Name, CHARINDEX( ':', cd.Name ))))
ELSE
RIGHT( cd.Name, LEN( CHARINDEX( ' ', REVERSE( cd.Name )) -1 ) )
END
AS [Candidate First Name]
, CASE
WHEN CHARINDEX(':', cd.Name) <> 0 THEN
LEFT( cd.Name, LEN( LEFT( cd.Name, CHARINDEX( ':', cd.Name ) -1 )))
ELSE
LEFT( cd.Name, LEN( cd.Name ) - CHARINDEX( ' ', REVERSE( cd.Name )))
END
AS [Candidate Last Name]
, ce.[CanSid] AS [Candidate Number]
, ck.[UCI] AS [Unique Identifier]
, cd.[DOB] AS [DoB]
, q.[ShortName] AS [Exam]
, q.[Name] AS [Exam Level]
, rig.[KAD] AS [Key Assessment Date]
, CASE
WHEN LEFT( q.[ShortName], 2 ) = 'CB' THEN 'CB'
WHEN LEFT( q.[ShortName], 2 ) = 'PB' THEN 'PB'
ELSE 'Other'
END
AS [CB or PB]
, ct.[CnuId] AS [Centre No]
, ct.[Name] AS [Centre Name]
, ct.[Active] AS [Centre Status]
, ct.[EsolTown] AS [Country Exam taken in]
, NULL AS [Centre Region]
, NULL AS [Nationality]
, cd.[Sex] AS [Gender]
, NULL AS [Special Arrangement Flag Y/N]
, ce.[ResultInfo] AS [ResultInfo]
FROM [dbo].[CandidateEntries] ce
JOIN [dbo].[Candidates] cd ON ce.[CanSid] = cd.[CanSid]
JOIN [dbo].[CandidateKeys] ck ON ce.[CanSid] = ck.[CanSid]
JOIN [dbo].[RegisteredCandidate] rc ON ck.[UCI] = rc.[UCI]
JOIN [dbo].[Centres] ct ON ce.[CnuId] = ct.[CnuId]
JOIN [dbo].[Qualifications] q ON ce.[QuaSid] = q.[QuaSid]
JOIN [dbo].[SessionQuals] sq ON ce.SesSid = sq.SesSid
JOIN [dbo].[ResultIssueGroups] rig ON sq.RigSid = rig.RigSidThe ce.ResultsInfo value will be as follows:
As you can see it is | delimited. From within the SQL query I need a way to split the values (whether they are NULL or not) into separate columns for the row. The number of | should be 18. So that will be 19 columns to be returned. I would like to be able to Alias each column too (A, B, C, etc..) will be fine but if this adds to complexity then I can just name them on the export of the SQL query.
Please can someone nudge me in the right direction on how to achieve this please? Preferably dynamically in case there aren't always 18 delimters (|) present.
Thanks.It would look something like the following:
SELECT
rc.[EmailAddress] AS [Email Address]
, cd.[Name] AS [Candidates Last : Candidate First]
, CASE
WHEN CHARINDEX(':', cd.Name) <> 0 THEN
RIGHT(cd.Name, LEN(RIGHT(cd.Name, LEN(cd.Name))) -
LEN(RIGHT(cd.Name, CHARINDEX(':', cd.Name ))))
ELSE RIGHT(cd.Name, LEN(CHARINDEX(' ', REVERSE(cd.Name)) -1))
END AS [Candidate First Name]
, CASE
WHEN CHARINDEX(':', cd.Name) <> 0 THEN
LEFT(cd.Name, LEN(LEFT(cd.Name, CHARINDEX(':', cd.Name) -1)))
ELSE LEFT(cd.Name, LEN(cd.Name ) - CHARINDEX(' ', REVERSE(cd.Name)))
END AS [Candidate Last Name]
, ce.[CanSid] AS [Candidate Number]
, ck.[UCI] AS [Unique Identifier]
, cd.[DOB] AS [DoB]
, q.[ShortName] AS [Exam]
, q.[Name] AS [Exam Level]
, rig.[KAD] AS [Key Assessment Date]
, CASE
WHEN LEFT(q.[ShortName], 2) IN ('CB', 'PB') THEN LEFT(q.[ShortName], 2)
ELSE 'Other'
END AS [CB or PB]
, ct.[CnuId] AS [Centre No]
, ct.[Name] AS [Centre Name]
, ct.[Active] AS [Centre Status]
, ct.[EsolTown] AS [Country Exam taken in]
, NULL AS [Centre Region]
, NULL AS [Nationality]
, cd.[Sex] AS [Gender]
, NULL AS [Special Arrangement Flag Y/N]
, ce.[ResultInfo] AS [ResultInfo]
, SP.[1] AS xxx1
, SP.[2] AS xxx2
, SP.[3] AS xxx3
, SP.[4] AS xxx4
, SP.[5] AS xxx5
, SP.[6] AS xxx6
, SP.[7] AS xxx7
, SP.[8] AS xxx8
, SP.[9] AS xxx9
, SP.[10] AS xxx10
, SP.[11] AS xxx11
, SP.[12] AS xxx12
, SP.[13] AS xxx13
, SP.[14] AS xxx14
, SP.[15] AS xxx15
, SP.[16] AS xxx16
, SP.[17] AS xxx17
, SP.[18] AS xxx18
, SP.[19] AS xxx19
FROM [dbo].[CandidateEntries] AS ce
JOIN [dbo].[Candidates] AS cd
ON ce.[CanSid] = cd.[CanSid]
JOIN [dbo].[CandidateKeys] AS ck
ON ce.[CanSid] = ck.[CanSid]
JOIN [dbo].[RegisteredCandidate] AS rc
ON ck.[UCI] = rc.[UCI]
JOIN [dbo].[Centres] AS ct
ON ce.[CnuId] = ct.[CnuId]
JOIN [dbo].[Qualifications] AS q
ON ce.[QuaSid] = q.[QuaSid]
JOIN [dbo].[SessionQuals] AS sq
ON ce.SesSid = sq.SesSid
JOIN [dbo].[ResultIssueGroups] AS rig
ON sq.RigSid = rig.RigSid
OUTER APPLY (
SELECT [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13],
[14], [15], [16], [17], [18], [19]
FROM dbo.DelimitedSplit8K_LEAD(ce.ResultInfo, '|')
PIVOT (MAX(Item) FOR ItemNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13],
[14], [15], [16], [17], [18], [19])) AS PVT
) AS SPYou'd want to change the output column names at the end of the SELECT to match your needs.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply