SQL Server - How to display master details data in columns

I have two tables, to be concise let’s call them TableA and TableB. This is the schema:

TableA
ID – int
Name varchar(50)

TableB
ID – int
TableA_Fk – int
Value varchar(50)

Each record in table A can have at most 9 records in table B. I want to be able to retrieve the data in a columnar form:

TableA-Name, TableB-Value1, … TableB-Value9

Is this possible using queries? Thanks!

From stackoverflow

• You will require a LEFT JOIN and a PIVOT table

• You could do something like:

  SELECT rank() OVER (ORDER BY tableA_FK) as rank, tableA_fk, value
  INTO #temp
  FROM TableB b
  ORDER BY rank 
  
  
  SELECT a.Name, 
         CASE WHEN t.rank = 1 THEN t.Value ELSE NULL END AS TableB-Value1,
         CASE WHEN t.rank = 2 THEN t.Value ELSE NULL END AS TableB-Value2,
         CASE WHEN t.rank = 3 THEN t.Value ELSE NULL END AS TableB-Value3,
         .... (etc.)
  FROM TableA a
  INNER JOIN #temp t ON a.Id = t.tableA_fk
  

  You need Sql Server 2005 or up. Sorry, but I don't have Sql Server (or the time) to test this well. Hope this gives you an idea and helps.

  OMG Ponies : You'll want to partition by the fk rather than ORDER

• This should do it, in addition to be DBRM independant.

  SELECT A.Name
      , SUM(CASE WHEN B.Value = 1 THEN 1 ELSE NULL END) AS B_Value_1
      , SUM(CASE WHEN B.Value = 2 THEN 2 ELSE NULL END) AS B_Value_2
      , SUM(CASE WHEN B.Value = 3 THEN 3 ELSE NULL END) AS B_Value_3
      , SUM(CASE WHEN B.Value = 4 THEN 4 ELSE NULL END) AS B_Value_4
      , SUM(CASE WHEN B.Value = 5 THEN 5 ELSE NULL END) AS B_Value_5
      , SUM(CASE WHEN B.Value = 6 THEN 6 ELSE NULL END) AS B_Value_6
      , SUM(CASE WHEN B.Value = 7 THEN 7 ELSE NULL END) AS B_Value_7
      , SUM(CASE WHEN B.Value = 8 THEN 8 ELSE NULL END) AS B_Value_8
      , SUM(CASE WHEN B.Value = 9 THEN 9 ELSE NULL END) AS B_Value_9
  FROM A
      INNER JOIN B ON B.TableA_FK = A.ID
  GROUP BY A.Name
  ORDER BY A.Name