Split column value to match yes or no

Question

I have two tables named Retail and Activity and the data is as shown below:

Retail Table

enter image description here

Activity Table

enter image description here

My main concern is about Ok and Fault column of the table Retail, as you can see it contains comma separated value of ActivityId.

What i want is, if the Ok column has ActivityId the corresponding column will have Yes, if the Fault column has ActivityId then it should be marked as No

Note I have only four columns that is fixed, it means i have to check that either four of the columns has its value in Ok or Fault, if yes then only i have to print yes or no, otherwise null.

Desired result should be like : If the value is in Ok then yes other wise No.

enter image description here

@HarshSharma Which RDBMS are you using? Keep proper tag of dabase and remove other tags — Saharsh Shah, Dec 31 '14 at 07:42
What do you mean by corresponding column? Which column should have yes/no values? — danish, Dec 31 '14 at 07:48
first try something, then post here what errors are you facing. — Pathik Vejani, Dec 31 '14 at 07:58
What if OK and Fault both have values? Show query you have tried so far. — danish, Dec 31 '14 at 08:01

score 0 · Answer 1 · answered Dec 31 '14 at 07:50

0

I guessing you want to store 'yes' or 'No' in some column. Below is the query to update that column :

UPDATE RetailTable
 SET <Result_Column>=
  CASE 
  WHEN Ok IS NOT NULL THEN 'Yes'
  WHEN Fault IS NOT NULL THEN 'No'
  END

answered Dec 31 '14 at 07:50

Kishore Kumar

78
6

score 0 · Answer 2 · edited May 23 '17 at 12:22

You can use below code as staring point:

DECLARE @Retail TABLE
(
PhoneAuditID INT,
HandsetQuoteID INT,
Ok VARCHAR(50)
)
INSERT INTO @Retail VALUES (1, 1009228, '4,22,5')
INSERT INTO @Retail VALUES (2, 1009229, '1')

DECLARE @Activity TABLE
(
ID INT,
Activity VARCHAR(50)
)
INSERT INTO @Activity VALUES (1, 'BatteryOK?'), (4, 'PhonePowersUp?'), (22,'SomeOtherQuestion?'), (5,'LCD works OK?')

SELECT  R.[PhoneAuditID], R.[HandsetQuoteID], A.[Activity], [Ok] = CASE WHEN A.[ID] IS NOT NULL THEN 'Yes' END
FROM    @Retail R
CROSS APPLY dbo.Split(R.Ok, ',') S
LEFT JOIN @Activity A ON S.[items] = A.[ID]

I have used Split function provided here: separate comma separated values and store in table in sql server

score 0 · Answer 3 · answered Dec 31 '14 at 09:45

Try following query. i have used pivot to show row as columns. I have also used split function to split id values which you can find easily on net:

CREATE TABLE PhoneAudit
(
    PhoneAuditRetailID INT,
    HandsetQuoteID INT,
    Ok VARCHAR(50),
    Fault VARCHAR(50)
)

INSERT INTO PhoneAudit VALUES (1,10090,'1,2','3')

CREATE TABLE ActivityT
(
    ID INT,
    Activity VARCHAR(100)
)

INSERT INTO ActivityT VALUES (1,'Battery')
INSERT INTO ActivityT VALUES (2,'HasCharger')
INSERT INTO ActivityT VALUES (3,'HasMemoryCard')
INSERT INTO ActivityT VALUES (4,'Test')

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Activity) FROM (SELECT DISTINCT Activity FROM ActivityT) AS Activities

SET @SQL = 'SELECT PhoneAuditRetailID, HandsetQuoteID,
  ' + @ColumnName + '
FROM
(SELECT
    t1.PhoneAuditRetailID,
    t1.HandsetQuoteID,
    TEMPOK.*
FROM
    PhoneAudit t1
CROSS APPLY
(
    SELECT
        Activity,
        (CASE WHEN ID IN (SELECT * FROM dbo.SplitIDs(t1.Ok,'','')) 
        THEN ''YES'' 
        ELSE ''NO''
        END) AS VALUE
    FROM
        ActivityT t2
) AS TEMPOK) AS t3
PIVOT
(
    MIN(VALUE)
    FOR Activity IN ('+ @ColumnName + ')
) AS PivotTable;'

EXEC sp_executesql @SQL

DROP TABLE PhoneAudit
DROP TABLE ActivityT

score 0 · Answer 4 · answered Dec 31 '14 at 11:13

There are several ways to do this. If you are looking for a purely declarative approach, you could use a recursive CTE. The following example of this is presented as a generic solution with test data which should be adaptable to your needs:

Declare @Delimiter As Varchar(2)

Set @Delimiter = ','

Declare @Strings As Table
(
    String Varchar(50)
)

Insert Into @Strings
Values
    ('12,345,6,78,9'),
    (Null),
    (''),
    ('123')

;With String_Columns As
(
    Select
       String,
       Case
          When String Is Null Then ''
          When CharIndex(@Delimiter,String,0) = 0 Then ''
          When Len(String) = 0 Then ''
          Else Left(String,CharIndex(@Delimiter,String,0)-1)
       End As String_Column,
       Case
          When String Is Null Then ''
          When CharIndex(@Delimiter,String,0) = 0 Then ''
          When Len(String) = 0 Then ''
          When Len(Left(String,CharIndex(@Delimiter,String,0)-1)) = 0 Then ''
          Else Right(String,Len(String)-Len(Left(String,CharIndex(@Delimiter,String,0)-1))-1)
       End As Remainder,
       1 As String_Column_Number
    From
       @Strings
    Union All
    Select
       String,
       Case
          When CharIndex(@Delimiter,Remainder,0) = 0 Then Remainder
          Else Left(Remainder,CharIndex(@Delimiter,Remainder,0)-1)
       End As Remainder,
       Case
          When CharIndex(@Delimiter,Remainder,0) = 0 Then ''
          When Len(Left(Remainder,CharIndex(@Delimiter,Remainder,0)-1)) = 0 Then ''
          Else Right(Remainder,Len(Remainder)-Len(Left(Remainder,CharIndex(@Delimiter,Remainder,0)-1))-1)
       End As Remainder,
       String_Column_Number + 1
    From
       String_Columns
    Where
       (Remainder Is Not Null And Len(Remainder) > 1)
)
Select
    String,
    String_Column,
    String_Column_Number
From
    String_Columns

Split column value to match yes or no

4 Answers4