sql server 2008 - SQL - Lookup multiple values from same field -

The problem and the desired result are explained in the SQL code below. I had a solution to work partially using a cursor and loop sub-statement, but was still struggling with the fact that any number of user ID in the same area (for example 0-10 ) And some users may not even have a valid match in the lookup table.

Example Data Source Table We need to find the user name based on user ID

  drop tables #sourcetable table #sourceset (queued int, user id varchar (50) ) Enter #sourcetable value Insert #SourCetable value ('1', '123456789') Insert ('2', '123456789' + ',' + '456821495') Insert #sourcetable value ('3', '123456789' + ',' + '456821495' + ',' + '589642304')  

Example lookup table is a table with all the users listed by the User ID Insert the #lookuptable value ('123456789', 'User A'), insert the drop table #lookuptable table #lookuptable (userId varchar (50), user name varchar (100)) # Insert the lookupable value ('456821495', 'User B') #lookuptable value ('58 9642304 ',' User C ')

This is the expected result. The point is that there can be multiple (any number) user id in the 1 userid field in the source table. The only stable factor is that the valid user ID is always 9 digits and each area will be different, if there are many IDs in the area. (SQL v2008 server)

  drop table # create a reference table # Solution (row int, user id varchar (50), username varchar (100)) # Enter the result value ('1', '123456789 ',' User A ') Insert #Resulttable Value (' 2 ',' 123456789 '+', '+' 456821495 ',' User A, User B ') # Solutionable Value (' 3 ',' 123456789 '+', '+' 456821495 '+', '+' 589642304 ',' User A, User B, User C ') * Select from #lookuptable * from #sourcetable Choose from * #RestaltTable  / Pre> 

I recommend changing the way your data is stored. SQL Server is not optimized to have multiple values ​​in the same area. If you can not change the way data is stored, here's a way to get the results you want (I clean my code ).

  IF OBJECT_ID ('tempdb .. #sourcetable') Null drop table #sourceTable table is not to make #sourcetable (line input, user id varchar (50)); INSERT #Sourceetable VALUES ('1', '123456789'), ('2', '123456789', '+' 456821495 '), (' 3 ',' 123456789 '+', '+' 456821495 '+', ' + '589,642,304'); IF OBJECT_ID ('tempdb .. # lookupable') Null drop table #lookupTable is not; Create table #lookuptable (userId varchar (50), username varchar (100)); INSERT #lookuptable VALUES ('123456789', 'User A'), ('456821495', 'userb'), ('589642304 ',' user c '); IF OBJECT_ID ('tempdb .. # ResultTable') is NOT NULL DROP TABLE # ResultTable Create Table # Resulttable (line INT, UserAdded VARCHAR (50), Username VARCHAR (100)); INSERT #ResulttableVALUES ('1', '123456789', 'User A'), ('2', '123456789' +, '+' 456821495 ',' User A, User B '), (' 3 ',' 123456789 '+', '+' 456821495 '+', '+58 9 642304', 'User A, User B, User C'); With the '%' + CAST (AussariID) form, such as CTE_Results (select B.ROID, B.USERId, A.Username #lookuptable, on a left JOIN #Sourceetable B on CAST (BSIARIIS VARCHAR (100)) VARCHAR 10)) + '%') Select line, user id, stoof (Order by username, for 'select', 'username', CT E_Results A WHERE (AUCID = BUSIRID) xml path ('')). User ID b group quote, userId; with CTE_R result as '(./text ()) [1]', 'VARCHAR (MX)'), 1,1, ''  

Result:

  Line User ID Username ----------- ----------- - ---------------- ----------------------- 1 123456789 User A2 123456789,456821495 User A, user b3 123456789,456821495,589642304 user A, user b, user c