CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Jeff Martinez
Jeff Martinez

Reputation: 83

SQL Server 2012 Convert several comma delimited values to table rows/columns

Currently, I have a table that is storing historical data in the following fashion. I have no control over this server or how the data is stored.

    ID   |  FName  |  LName  |Stuff| More
 --------+---------+---------+-----+------
 1,2,3,4 | j,p,g,r | l,m,h,s | ,,, | a,,b,

I need to get this data into a result set so that it is in the following format:

    ID   |  FName  |  LName  |Stuff| More
 --------+---------+---------+-----+------
     1   |    j    |    l    |     | a
     2   |    p    |    m    |     | 
     3   |    g    |    h    |     | b
     4   |    r    |    s    |     |

I would like to avoid using a function as I am unsure of the access I will have to the servers in other environments. I have tried using xml with cross apply, which I can get to work for a singular field, but I cannot seem to get the full table to work.

Any suggestions would be greatly appreciated,

Thanks ~JM

Upvotes: 0

Views: 83

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If, you want to avoid use of UDF, then XML nodes() method still able to help you by using multiple CTE approach. 

WITH cteId AS
(
    SELECT Ids.value('.', 'INT') Id FROM
    (
       SELECT 
         cast('<x>'+replace(Id, ',', '</x><x>')+'</x>' as xml) as Id 
        FROM table t 
    )a CROSS APPLY Id.nodes ('/x') as split(Ids)
), ctefname AS
(
    SELECT
            row_number() over (order by (select 1)) Seq,
            FNames.value('.', 'varchar') FNames FROM
    (
       SELECT 
         cast('<x>'+replace(FName, ',', '</x><x>')+'</x>' as xml) as FName 
       FROM table t 
    )a CROSS APPLY FName.nodes ('/x') as split(FNames)
), cteLname AS
(
    SELECT
            row_number() over (order by (select 1)) Seq,
            LNames.value('.', 'varchar') LNames FROM
    (
       SELECT
           cast('<x>'+replace(LName, ',', '</x><x>')+'</x>' as xml) as LName 
       FROM table t 
    )a CROSS APPLY LName.nodes ('/x') as split(LNames)
), ...

SELECT 
       id.Id, fn.FNames, ln.LNames, ... 
FROM cteId id
INNER JOIN ctefname fn on fn.Seq = id.Id
INNER JOIN cteLname ln on ln.Seq = id.Id
...

Upvotes: 0

Przemek Filipczyk
Przemek Filipczyk

Reputation: 68

It can be done by recursive query

;WITH CTE(ID, ID_tmp, FName, FName_tmp, LName, LName_tmp, Stuf, Stuf_tmp, more, more_tmp) 
AS
(
    SELECT CAST(LEFT(ID, CHARINDEX(',',ID+',')-1) AS NVARCHAR(50)) ID,
            STUFF(ID, 1, CHARINDEX(',',ID+','), '') ID_tmp,
            CAST(LEFT(FName, CHARINDEX(',',FName+',')-1) AS NVARCHAR(50)) FName,
            STUFF(FName, 1, CHARINDEX(',',FName+','), '') ID_tmp,
            CAST(LEFT(LName, CHARINDEX(',',LName+',')-1) AS NVARCHAR(50)) LName,
            STUFF(LName, 1, CHARINDEX(',',LName+','), '') LName_tmp,
            CAST(LEFT(Stuf, CHARINDEX(',',Stuf+',')-1) AS NVARCHAR(50)) Stuf,
            STUFF(Stuf, 1, CHARINDEX(',',Stuf+','), '') Stuf_tmp,
            CAST(LEFT(more, CHARINDEX(',',more+',')-1) AS NVARCHAR(50)) more,
            STUFF(more, 1, CHARINDEX(',',more+','), '') more_tmp
    FROM    TAB
        UNION ALL
    SELECT CAST(LEFT(ID_tmp, CHARINDEX(',',ID_tmp+',')-1) AS NVARCHAR(50)) ID,
        STUFF(ID_tmp, 1, CHARINDEX(',',ID_tmp+','), '') ID_tmp,
        CAST(LEFT(FName_tmp, CHARINDEX(',',FName_tmp+',')-1) AS NVARCHAR(50)) FName,
        STUFF(FName_tmp, 1, CHARINDEX(',',FName_tmp+','), '') FName_tmp,
        CAST(LEFT(LName_tmp, CHARINDEX(',',LName_tmp+',')-1) AS NVARCHAR(50)) LName,
        STUFF(LName_tmp, 1, CHARINDEX(',',LName_tmp+','), '') LName_tmp,
        CAST(LEFT(Stuf_tmp, CHARINDEX(',',Stuf_tmp+',')-1) AS NVARCHAR(50)) Stuf,
        STUFF(Stuf_tmp, 1, CHARINDEX(',',Stuf_tmp+','), '') Stuf_tmp,
        CAST(LEFT(more_tmp, CHARINDEX(',',more_tmp+',')-1) AS NVARCHAR(50)) more,
        STUFF(more_tmp, 1, CHARINDEX(',',more_tmp+','), '') more_tmp
    FROM CTE
    WHERE ID_tmp > ''
)
SELECT  ID, FName , LName, stuf, more
FROM    CTE

enter image description here

Upvotes: 1

Related Questions