Solved

Combine related data from 2 tables with multiple rows into one result

Posted on 2008-06-11
4
245 Views
Last Modified: 2010-04-21
Table 1
ID     DefectID
---------------------------
A     7
A     9
A     2
B     7
B     4

Table 2
ID     Label     Value
---------------------------
A     Grade    S
A     Length     1.5
A     Width     47
B     Grade    Z
B     Length     6.5
B     Width     23

I need to return the total count of defects for each ID from Table 1, and include the related data from Table 2.  The results should look like this:

ID     Count(DefectID)     Grade     Length     Width
A     3                             S            1.5            47
B     2                             Z             6.5           23



0
Comment
Question by:sainiak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 3

Accepted Solution

by:
cpleong99 earned 500 total points
ID: 21761323
select table1.id, 'Count(DefectID)' = count(distinct defectid),
      'Grade' = (select Value from table2 where table2.id=table1.id and Label ='Grade'),
      'Length' = (select Value from table2 where table2.id=table1.id and Label ='Length'),
      'Width' = (select Value from table2 where table2.id=table1.id and Label ='Width')
from table1
group by table1.id
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21761341
What you want to do is called a 'pivot', and if you know what the columns will be you can use a cross tab query to do it.  In SQL Server 2005 there is a PIVOT keyword that will help.  In SQL Server 2000 it's a little more complicated.  Either way, you do have to know what columns you need in advance.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21761848
SELECT t2.ID, COALESCE(t1.DefectCount, 0) AS DefectCount,
    t2.Grade, t2.Length, t2.Width
FROM (
    SELECT ID,
        MAX(CASE WHEN Label = 'Grade' THEN Value ELSE '' END) AS Grade,
        MAX(CASE WHEN Label = 'Length' THEN Value ELSE '' END) AS Length,
        MAX(CASE WHEN Label = 'Width' THEN Value ELSE '' END) AS Width
    FROM [Table 2]
    GROUP BY ID
) AS t2
LEFT OUTER JOIN (
    SELECT ID, COUNT(DefectID) AS DefectCount
    FROM [Table 1]
    GROUP BY ID
) AS t1 ON t1.ID = t2.ID
ORDER BY t2.ID
0
 

Author Closing Comment

by:sainiak
ID: 31466196
This worked great, and was much simpler than the other suggestions.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question