SELECT f.id,f.name,b.Bar_name,IDENTITY(int,1,1) AS RowID
INTO #Temp
FROM (SELECT id,
             name,
             Bar_id1 as Bar_id
      FROM Foo 
      UNION ALL
      SELECT id,
             name,
             Bar_id2 as Bar_id
      FROM Foo 
      UNION ALL
      SELECT id,
             name,
             Bar_id3 as Bar_id
      FROM Foo 
      UNION ALL
      ....
      SELECT id,
             name,
             Bar_id8 as Bar_id
      FROM Foo )f
INNER JOIN Bar b
ON b.Bar_id=f.Bar_id

SELECT id,name,
MAX(CASE WHEN Seq=1 THEN Bar_name ELSE NULL END) AS Bar_name1,
MAX(CASE WHEN Seq=2 THEN Bar_name ELSE NULL END) AS Bar_name2,
.......
MAX(CASE WHEN Seq=8 THEN Bar_name ELSE NULL END) AS Bar_name8
FROM
(
SELECT (SELECT COUNT(*) FROM #Temp WHERE id=id and name =t.name 
        AND RowID<=t.RowID) AS Seq,*
FROM #Temp t
)r
GROUP BY id,name

DROP TABLE #Temp

;With CTE (Foo_id,Foo_name,Bar_id) AS
(
SELECT id,name,IDVal
FROM (SELECT * FROM Foo)t
UNPIVOT (IDVal FOR ID IN ([Bar_id1],[Bar_id2],...[Bar_Id8]))p
)

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY id,name ORDER BY BarId) AS Seq,*
FROM CTE c
INNER JOIN Bar b
OB b.Bar_id=c.Bar_Id
)m
PIVOT(MAX(Bar_name) FOR Seq IN ([1],[2],..))p