Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Do you only ever have 3 keys X, Y and Z? That is, fixed columns? –  gbn Jul 13 '11 at 8:36
    
@gbn Thanks. Unfortunately, this is dynamic. I plan to dynamically generate the SQL statements in code. I'll update the question. –  Uwe Keim Jul 13 '11 at 8:44
1  
You'd have to. It can't be done in SQL (unless dynamic SQL or XML) which won't be portable... –  gbn Jul 13 '11 at 8:48
1  
Yes, I'd consider lots of self joins per key. However, it will run badly for more than a dozen. TBH, reporting services controls like the Tablix or Matrix will do this for me, especially this looks like a report. –  gbn Jul 13 '11 at 9:00
2  
show 2 more comments

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
The above doesn't seem to work if you put: min(case when [key] = 'X' then 1 else 0 end) as 'x' instead of using null –  user15302 Nov 13 '12 at 15:01
    
why would you put that then 1 else 0 instead of the working correctly then Value else null? –  ypercube Nov 13 '12 at 15:07
    
@vig I am not sure why you would want to change that to min(case when [key] = 'X' then 1 else 0 end), can you explain the need to do that? It won't get you the correct result which is to display the name. –  bluefeet Nov 13 '12 at 15:40
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.