I am working in an existing stored procedure. In that, they fetch some data using select Queries. Now I am trying to fetch some more data. The problems I have is, the column I am using to fetch data from another table have more than one Id saved in with comma(,) in between.

Procedure with 2 tables as Tbl_av,Tbl_aa.

I have one column in Tbl_av as processId that is in Varchar and it stores data like 1,2,4 etc.. (that is, it stores the Id of the Process).

Now I am trying to get the Process Name from one more table Tbl_Process using this ProcessId under the table Tbl_av. In that table, the process Id is Unique one and it is in INTEGER.

Tbl_Process as follows:

Tbl_av as follows:

I have procedure few part as follows:

SET @strQuery= 'SELECT isnull(av.ID, 0) AS ID
      ,isnull(ItemNumber, '''') AS Number  
      ,isnull(av.ItemName,'''') as Item Name
      ,av.Description
      ,(select top 1 Name from TBL_Names where Id =aa.id)as Person Incharge from Tbl_AV av, Tbl_aa aa WHERE av.S_number = aa.S_number'

Now what I am trying to do is , I need to fetch that Process names from Tbl_Process using this Tbl_av ProcessId's inside the procedure.

I dont know how to achieve it, as it both columns are in different datatype and more than 1 id is saved in one column

NOTE: We can simply achieve this as 'SELECT Process_Name from Tbl_Process WHERE Id in (av.ProcessId)' - But doing this ,displays data in column format.. I want data to be selected as if Id is 1,2 means I want my output as Process_Name = Item 1,Item 2.

Kindly help.

CodePudding user response：

Two tricks are needed. One is joining based on a comma-separated list of IDs. That can easily be done poorly resulting in unwanted matches such as 1 and 2 matching 12. The article Stephen Jennings referenced has some good reliable solutions.

The second is concatenating a collection of results into a single string. For recent versions of SQL Server, the STRING_AGG is the preferred solution. For older versions (such as 2014) the most common method is the "FOR XML" trick.

I've combined the two techniques below.

DECLARE @Tbl_Process TABLE(ID INT, process_Name VARCHAR(100))
INSERT @Tbl_Process
VALUES (1, 'Item 1'), (2, 'Item 2'), (3, 'Item 3'), (4, 'Item 4'), (5, 'Item 5'), (12, 'Item 12')

DECLARE @Tbl_av TABLE(ID INT, ProcessId VARCHAR(100))
INSERT @Tbl_av
VALUES (1, '1,3,5'), (2, '2,4'), (3, '1,2,3'), (4, '1,5'), (5, ''), (6, '3,4,12')

SELECT AV.*, PL.*
FROM @Tbl_av AV
CROSS APPLY (
    SELECT ISNULL(REPLACE(REPLACE(REPLACE(STUFF(
        (
            SELECT ','   P.process_Name
            FROM @Tbl_Process P
            --(bad) WHERE CHARINDEX(CONVERT(VARCHAR, P.ID), AV.ProcessId) > 0
            WHERE ','   AV.ProcessId   ',' LIKE '%,'   CONVERT(VARCHAR, P.ID)   ',%'
            ORDER BY P.ID -- alternately ORDER BY P.process_Name
            FOR XML PATH('')
        )
    , 1, 1, '')
    , '&lt;', '<'), '&gt;', '>'),'&amp;', '&') 
    , '(none)')
    AS Process_Names
) PL

Results

The STUFF() removes the leading comma, the REPLACE() calls restore certain characters that get encoded by FOR XML, and the ISNULL() provides a default if there are no values. See How Stuff and 'For Xml Path' work in SQL Server? for more on how the FOR XML trick works.

If you prefer comma-space list separators, you can update the ',', but will also need to adjust the STUFF to strip two characters instead of one.

The contents of the cross apply could be moved directly into the SELECT clause, but as a matter of style, the CROSS APPLY allows separation of complex logic from the rest of the query.