SQL statement to combine main table and child table as columns?

Question

Having a main table and a child table in a Microsoft SQL Server database (2005+), I want to show the user a single table in a grid view which includes rows from the main table and rows from the child table, with the latter one being shown as additional columns.

Please see the following example.

Main table:

ID | Name
---+--------
20 | Thomas
21 | Martin
22 | John
23 | Mike

Child table:

ID | MainID | Key | Value
---+--------+-----+-----------
1  | 20     | X   | Apple
2  | 20     | Y   | Microsoft
3  | 20     | Z   | Goole
4  | 21     | X   | Dell
5  | 22     | X   | Xing
6  | 22     | Y   | LinkedIn
7  | 22     | Z   | Cisco
8  | 22     | A   | Redhat
9  | 23     | Y   | Logitech
10 | 23     | Z   | Adobe

Now what I want to generate is an SQL statement that results in the following result set:

ID | Name   | X      | Y         | Z
---+--------+--------+-----------+--------
20 | Thomas | Apple  | Microsoft | Google
21 | Martin | Dell   | <null>    | <null>
22 | John   | Xing   | LinkedIn  | Cisco
23 | Mike   | <null> | Logitech  | Adobe

I.e. the child rows are transformed as additional columns.

My question is:

Is there a way to achieve this with standard SQL, i.e. without using the PIVOT statement or some XML statements? (because I want to use the query against a VistaDB database, too).

Update 1

Then number of detail keys is variable and not limited to 3 or to "X", "Y" and "Z" as names.

Answer 1

There are two ways that I see you can do this - one would be with a static query the other with dynamic SQL.

Static query (even though you stated you have an unknown number of columns - this is how you would do this).:

select m.id,
  min(case when [key] = 'X' then value else null end) as 'x',
  min(case when [key] = 'y' then value else null end) as 'y',
  min(case when [key] = 'z' then value else null end) as 'z'
from main m
left join child c
  on m.id = c.mainid
group by m.id

see SQL Fiddle with Demo

Dynamic SQL version, this will create the query:

DECLARE @query  AS NVARCHAR(MAX) = ' '
DECLARE @rowcount int
DECLARE @rowNumber int = 0
DECLARE @case as nvarchar(max) = ' '
DECLARE @keyValue as varchar(1)

select distinct [key]
into #temp
from child

-- get the number of unique keys
set @rowcount = (select count(*) from #temp)

while @rowNumber < @rowcount
  begin
    set @keyValue = (select top 1 [key] from #temp)

    set @case 
      = @case + 'min(case when [key] = ''' + @keyValue 
          + ''' then value else null end) as ''' + @keyValue + ''', '

    if @rowNumber < @rowcount
      begin
        set @rowNumber = @rowNumber + 1

        delete from #temp where [key] = @keyValue
      end
  end

set @query 
  = 'select m.id, '+ substring(@case, 1, len(@case) -1) + '
     from main m
     left join child c
       on m.id = c.mainid
     group by m.id'

exec(@query)

see SQL Fiddle with Demo

The static version is portable but will not work with your dynamic requirement.

Answer 2

The only way I see this getting done is to use temp tables and looping inside a stored procedure.