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

Question

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.

It would be far better if you could change the design and use one of the data types that SQL Server supports for actually storing multiple items - which would be XML or a table. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 14, 2014 at 6:26

Mukund · Accepted Answer · 2014-10-14 06:24:05Z

3

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

answered Oct 14, 2014 at 6:24

Mukund

1,6871 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Al__H Over a year ago

thanks! One follow-up question.. I found this field can have 6 assignees. How would I modify the statement to do accommodate this? Thanks!

Mukund Over a year ago

Nothing will change, only you need to add logic for getting extra request value. Like Request[4]....

Al__H Over a year ago

I tried that, but ran into problems with the Convert statement. ` SELECT CONVERT(XML,'<Requests><Request>' + REPLACE(commaValue,',', '</Request><Request>') + '</Request></Requests>') AS xmlname FROM Comma` I couldn't it to work.

Mukund Over a year ago

No you don't need to change anything there. I think the problem in sqlfiddle. You can check it with sqlserver. It should work fine there.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related