0

I have this column in a SQL Server table of type nvarchar that has Id numbers of another table separated by semicolon (bad design, can't change it).

enter image description here

enter image description here

The query I am trying to run works when there is only one value in the GroupIds column however with multiple groups it fails with an error

Conversion failed when converting the nvarchar value '2;5' to data type int.

Code:

SELECT 
    i.Name,
    i.Channel,
    g.Name AS 'Group Name',
    io.Name AS 'Logger Name',
    io.ModuleType,
    io.IpAddress
FROM 
    Input i
JOIN 
    [dbo].[Group] g ON i.GroupIds = g.Id
JOIN 
    [dbo].[IoModule] io ON i.IoModuleId = io.Id
ORDER BY
    i.Channel, io.Name;

I have exhausted my Google skills with a few things half working but nothing giving the results I need. Ideally, I would like to see a result with the Group Names separated by commas.

Note: I need this as a one-off report from a production database. I can't create temp tables, functions or anything of that nature. The answer that this question has been tagged as duplicate is not any help. There are 42 answers from the past 10 years in that question. None of those had anything about selecting that data from a join. It may be my general lack of knowledge of joins but I am searching for an answer of basically getting my group ids in one column by group name separated by a comma.

Switcher
  • 112
  • 12
  • Which data in GroupIds column that you want to compare to Input.Id, my friend? First, Last or Contain? – Tomato32 Sep 10 '18 at 14:18
  • Look closely at the answer from Aaron Bertrand at the duplicate. The ones with higher votes are NOT a good approach from a performance perspective. – Sean Lange Sep 10 '18 at 14:26
  • I think this is actually a join problem – S3S Sep 10 '18 at 14:26
  • @SeanLange While this may work I cannot create a function. I have been able to sucessfully select the split value with select left however I have not been able to do it with a join. – Switcher Sep 10 '18 at 14:36
  • 1
    @Switcher SQL Server 2016 introduced `SPLIT_STRING`. Aaron Bertrand wrote and benchmarked all possible techniques for previous versions [in his articles](https://sqlperformance.com/2016/03/sql-server-2016/string-split).The techniques that use loops are the slowest possible. The XML technique is probably the easiest to use - small enough that you can include it in your SQL string, possibly in a CTE – Panagiotis Kanavos Sep 10 '18 at 14:40
  • 1
    @Switcher you don't *have* to create a function to use any of these techniques. You can embed the expressions in your query but the result will be rather ugly – Panagiotis Kanavos Sep 10 '18 at 14:43

0 Answers0