locked
Need help relating data without relationship (many 2 many) RRS feed

  • General discussion

  • 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


    Friday, March 27, 2015 2:08 PM

All replies

  • 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
    Friday, March 27, 2015 2:24 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


    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.
    Friday, March 27, 2015 3:17 PM
  • Jon, have you made any progress on this?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, May 1, 2015 8:32 PM
  • Jon, have you made any progress on this?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Yes, I decided to go a different route.

    I created a new table and view to accomplish the task.  I also stopped using Power BI and used native SQL connect functionality in Excel to the data could be copied and manipulated by other users as needed.

    Thanks!

    Thursday, June 18, 2015 1:55 PM