CODEWITHSUNDEEP
sql-servert-sql
Al__H
Al__H

Reputation: 306

TSQL Parse single column into multiple columns/rows without a function

I'm trying to query a ticketing system, where multiple users can be assigned to a ticket at once.

It stores the assignees as comma-delimited values in a column AH.AssignedTo_ETrack_UserID.

For example: 429173,525304,390497

When I try to run the following, the JOIN fails because of the comma. 

SELECT 
    DISTINCT(RI.ID) as REQ_NUM
    ,HT.FullName
FROM 
    RequestInfo RI
LEFT OUTER JOIN 
    RequestAssignInfoHistory AH ON RI.ID = AH.RequestInfo_ID
LEFT OUTER JOIN 
    WA_Team HT ON AH.AssignedTo_ETrack_UserID = HT.ID

Error message:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value '429173,525304,390497' to data type int.

Any thoughts on how to do this?

I only have READ rights to this system, so I can't create a function.

Upvotes: 1

Views: 872

Answers (1)

Mukund
Mukund

Reputation: 1689

create table Comma(commaValue varchar(50))
go
insert into Comma Values('429173,525304,390497')
insert into Comma Values('429173,525304,390497')

;WITH Split_Requests (xmlname)
AS
(
    SELECT 
    CONVERT(XML,'<Requests><Request>'  
    + REPLACE(commaValue,',', '</Request><Request>') + '</Request></Requests>') AS xmlname
      FROM Comma
)

SELECT 
xmlname.value('/Requests[1]/Request[1]','varchar(25)') AS [Type1],
xmlname.value('/Requests[1]/Request[2]','varchar(25)') AS [Type2],
xmlname.value('/Requests[1]/Request[3]','varchar(25)') AS [Type3]
FROM Split_Requests

Now you can use this table in your query and apply join. Please also check http://sqlfiddle.com/#!3/7206a/1

Upvotes: 3

Related Questions