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

string-splittingt-sql

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

Best Answer

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.

Related Question