asked on

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

ASKER CERTIFIED SOLUTION

cpleong99

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joel Coehoorn

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.

Scott Pletcher

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

sainiak

ASKER

This worked great, and was much simpler than the other suggestions.