CODEWITHSUNDEEP
sqlsql-server
sonicbabbler
sonicbabbler

Reputation: 851

SQL Cross Apply with additional information

I have a table that is similar to this

ID | InvoiceNum | Client1 | Client2 | Client3 
1    123          Test2
2    1234         Abc                 Test1  
3    12345                  TestC

I want to search all 3 client fields and return to a single column with some additional information of which field it originated from.

ID | InvoiceNum | Client | Position
1    123          Test2    1
2    1234         Test1    3
3    12345        TestC    2

Here is my SQL query

Select id, invnum ,Client
From myTable A
Cross Apply (values (client1) ,(client2) ,(client3) ) B(Client)
Where Client like '%test%'
ORDER BY invnum

Using cross apply, I can get everything into a single column but am unsure of if/how I can get the position?

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Just add it into the values:

Select a.id, a.invnum, b.Client, b.position
From myTable A Cross Apply (values (client1, 1) ,(client2, 2), (client3, 3)
    ) B(Client, position)
where Client like '%test%'
order by a.invnum

Upvotes: 5

Related Questions