Solved

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

Posted on 2008-06-11
4
247 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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