I have the following data in two tables. I'd like to populate ColumnApps in Table1 with all the apps from Table2.  However the technique needs to be able to pickup a multi valued cell.  If there's no solution with a multi value cell how can I split the data out to get what I need?

Table1:

ColumnServer ColumnApps
------------ ---------------
ServerA Need to populate this, can be multi value
ServerB
ServerC

Table2:

ColumnApp ColumnServers
--------------- -------------------
App1 ServerB,ServerC
App2 ServerA,ServerB
App3 ServerA,ServerC

• Edited by JonSykes Friday, March 27, 2015 2:11 PM

Declare @column varchar(100) = 'SERVERB,SERVERC,SERVERA'
  Select @column YourColumn,
              PARSENAME(REPLACE(@column,',','.'),3)'Second',
              PARSENAME(REPLACE(@column,',','.'),2)'Third',
              PARSENAME(REPLACE(@column,',','.'),1)'Fourth'

OR 

select * from Table1 cross apply  Table2

Thanks

--------------------------------------------------------------------------------------------------

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

• Edited by SequelMate Friday, March 27, 2015 2:35 PM
• Proposed as answer by Ed Price - MSFTMicrosoft employee, Owner Thursday, April 02, 2015 5:47 PM
• Unproposed as answer by Michael AmadiModerator Sunday, April 19, 2015 12:34 AM

Declare @column varchar(100) = 'SERVERB,SERVERC,SERVERA'
  Select @column YourColumn,
              PARSENAME(REPLACE(@column,',','.'),3)'Second',
              PARSENAME(REPLACE(@column,',','.'),2)'Third',
              PARSENAME(REPLACE(@column,',','.'),1)'Fourth'

OR 

select * from Table1 cross apply  Table2

Thanks

--------------------------------------------------------------------------------------------------

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

I should have mentioned that table1 is in powerpivot within excel.  The data is initially pulled from sql.  However table2 is data from an excel spreadsheet.

Jon, have you made any progress on this?

Thanks!