asked
break 1 field into many based on delimter
I have a table in a MSSQL database with a field called classification and it contains data I want to split out into multiple fields in my select statement based on a delimiter. The delimiter equals a period (.) Would someone mind helping me out? Below is a snippet of the data in the classification field. I would like a new field for every delimiter occurrence. The number of occurences of the delimiter can vary so therefore varying number of new fields. So if i have 3 delimiters i have 4 fields needed, 4 delimters 5 fields etc. ****Classification**** Service Request.User Access.Departure.Corporate Employee.Master Ticket Service Request.User Access.Departure.PSC/LAB (non-mgr) Employee Service Request.User Access.New Hire.PSC/LAB (non-mgr) Employee Service Request.Reset Password.Other Service Request.Reset Password Service Request.User Access.Other Service Request.Reset Password.iLabLink (Health Care Provider Portal) Service Request.Reset Password.Active Directory (Windows) ---- Many, many thanks David
This seems like a relatively straightforward use of [Jeff Moden's split][1] function: DECLARE @TestData TABLE ( Classification VARCHAR(200) ); INSERT INTO @TestData VALUES ('Service Request.User Access.Departure.Corporate Employee.Master Ticket'), ('Service Request.User Access.Departure.PSC/LAB (non-mgr) Employee'), ('Service Request.User Access.New Hire.PSC/LAB (non-mgr) Employee'), ('Service Request.Reset Password.Other'), ('Service Request.Reset Password'), ('Service Request.User Access.Other'), ('Service Request.Reset Password.iLabLink (Health Care Provider Portal)'), ('Service Request.Reset Password.Active Directory (Windows)'); SELECT Classification, split.ItemNumber, split.Item FROM @TestData CROSS APPLY dbo.DelimitedSplit8k(Classification, '.') split; That gives you a table containing results such as: Classification ItemNumber Item Service Request.User Access.Departure.Corporate Employee.Master Ticket 1 Service Request Service Request.User Access.Departure.Corporate Employee.Master Ticket 2 User Access Service Request.User Access.Departure.Corporate Employee.Master Ticket 3 Departure Service Request.User Access.Departure.Corporate Employee.Master Ticket 4 Corporate Employee Service Request.User Access.Departure.Corporate Employee.Master Ticket 5 Master Ticket From that, you could then use one of various pivot options. I'm using the [cross tab][2], again documented by Jeff Moden...: WITH splitdata AS (SELECT Classification, split.ItemNumber, split.Item FROM @TestData CROSS APPLY dbo.DelimitedSplit8k(Classification, '.') split ) SELECT Classification, MAX(CASE WHEN itemnumber = 1 THEN item END) AS Item1, MAX(CASE WHEN itemnumber = 2 THEN item END) AS Item2, MAX(CASE WHEN itemnumber = 3 THEN item END) AS Item3, max(CASE WHEN itemnumber = 4 THEN item END) AS Item4, max(CASE WHEN Itemnumber = 5 THEN item END) AS Item5 FROM splitdata GROUP BY splitdata.Classification; [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/ [2]:
http://www.sqlservercentral.com/articles/T-SQL/63681/
17 People are following this question.
