Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

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
sainiak
Asked:
sainiak
1 Solution
 
cpleong99Commented:
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
 
Joel CoehoornDirector of Information TechnologyCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
sainiakAuthor Commented:
This worked great, and was much simpler than the other suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now