SQL Server - How to display master details data in columns

Question

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!

Answer 1

You will require a LEFT JOIN and a PIVOT table

Answer 2

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.

Answer 3

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