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

# 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     Length     1.5
A     Width     47
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:

A     3                             S            1.5            47
B     2                             Z             6.5           23

0
sainiak
1 Solution

Commented:
select table1.id, 'Count(DefectID)' = count(distinct defectid),
'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

Director 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

Senior DBACommented:
SELECT t2.ID, COALESCE(t1.DefectCount, 0) AS DefectCount,
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 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.