MS SQL 2012 - Need to split records from 4 different fields then query those

Question

So I have a table called 'WORK_ORDER' and the ID is a unique text example '16-0833' next would be 0834, 0835, etc.

In that table there are 4 user fields.. USER_1, USER_2, USER_3, USER_4..

This top work order needs x amount of other work orders to be completed.. so we will in those fields like this.

USER_1 = '16-0838' USER_2 = NULL USER_3 = '16-0834;16-0835;16-0835' USER_4 = NULL

I found a routing that will split them up and if I use this TSQL I get results.

---

DECLARE @UDF_1 nvarchar(100), @UDF_2 nvarchar(100), @UDF_3 nvarchar(100), @UDF_4 nvarchar(100), @ALL_UDF nvarchar(400);

SELECT        @UDF_1=USER_1, @UDF_2=USER_2, @UDF_3=USER_3, @UDF_4=USER_4
FROM            WORK_ORDER
WHERE        (BASE_ID = N'16-0833')

IF @UDF_1 IS NOT NULL
BEGIN
SET @ALL_UDF = @UDF_1
END

IF @UDF_2 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_2
END

IF @UDF_3 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_3
END

IF @UDF_4 IS NOT NULL
BEGIN
SET @ALL_UDF = @ALL_UDF + ';' + @UDF_4
END

BEGIN

SELECT
    *
FROM
    dbo.func_split(@ALL_UDF, ';')
END

---

With these results :

ElementID Element

1 16-0838

2 16-0834

3 16-0835

4 16-0836

I now need to take those 16-% numbers and requery the Work Order table to get their status, etc.

Just not quite sure how to accomplish this.

Any help is greatly appreciated. SQL 2012

Answer 1

Welcome to SQL, you can replace your sample code with this:

DECLARE @ParentBaseId nvarchar(100) = '16-0833'

SELECT * FROM WORK_ORDER
WHERE BASE_ID IN 
(
SELECT USER_1 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_2 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_3 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
UNION
SELECT USER_4 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId 
)

It's not the fanciest or even best way, but is the easiest to start learning with.

By the way, your code won't work if @UDF_1 is null, you need to assign '' to @ALL_UDF before working with it, or add other checks.

Answer 2

So this is what I ended up with that works. I am using a view to query the data. Now is it the absolute way to do it right? Not sure but I get the results I was looking for.

SELECT        A.[BASE_ID], Split.a.value('.', 'NVARCHAR(250)') AS INTMD_WO_ID
FROM            (SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_1], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER
                          UNION ALL
                          SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_2], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER
                          UNION ALL
                          SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_3], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER
                          UNION ALL
                          SELECT        [BASE_ID], CAST('<M>' + REPLACE([USER_4], ';', '</M><M>') + '</M>' AS XML) AS INTMD_WO_ID
                          FROM            WORK_ORDER) AS A CROSS APPLY INTMD_WO_ID.nodes('/M') AS Split(a)

Thanks.