?
Solved

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

Posted on 2008-06-11
4
Medium Priority
?
258 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
4 Comments
 
LVL 3

Accepted Solution

by:
cpleong99 earned 2000 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:Joel Coehoorn
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 70

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

621 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