Join 2 tables with 3rd table vertically I have 3 tables with id as common field. In 3rd table I have Lineindex,line columns which has multiple value like following :

Table A:

id   name
----------
1    name1


Table B:

id  value Aid
-------------
 1  val1  1


Table C:

id lineindex   line   Aid
--------------------------
1   1          lin1     1
2   2          lin2     1


Now, I want to take record by joining these table in a single row like'



a.id   name   b.id   value   line1[line+lineindex]   line2[line+lineindex]
-------------------------------------------------------------------------
 1     name1  1      val1    lin1                    lin2


Like, the line values in table- C need to get in single row.
How can we achieve this?
I achieved this by doing the following:

SELECT Id,Name,Num,TypeId,
[1] Line1,[2] Line2,[3] Line3,[4] Line4,[5] Line5,[6] Line6,[7] Line7,[8] Line8,[9] Line9,[10] Line10
,Location
from
(
SELECT ast.Id,ast.Name,ast.Num,ast.TypeId,
,loc.Location
FROM MyTable ast
LEFT JOIN MyLine astline ON ast.Id = astline.Id
LEFT JOIN aim.Location loc ON ast.LocationId = loc.LocationId
where ast.ProjectId = @ProjectId 
) as maintab1
pivot
( MIN(maintab1.Line)
   for maintab1.LineIndex in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) as pivotT1