June 21, 2012 at 2:02 pm
Any idea how I can list the alternate parts on the same row as the parent part? There may be several alternates for each parent. Any help will be appreciated.
SELECT p.PARTNUMBER
,p.DESCRIPTION
,a.ALTPART
FROM PART p
LEFT JOIN ALTERNATE_PART a on p.PARTNUMBER = a.PARTNUMBER
June 21, 2012 at 2:17 pm
Give this a try. It is untested as you didn't provide DDL, sample data, or expected results.
SELECT
p.PARTNUMBER,
p.DESCRIPTION,
STUFF((SELECT ',' + a.ALTPART
FROM dbo.ALTERNATE_PART a WHERE a.PARTNUMBER = p.PARTNUMBER
ORDER BY a.ALTPART
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS AltParts
FROM
dbo.PART p;
June 21, 2012 at 2:29 pm
That is cool and will get me by, but is there a way to list them into individual columns rather than stringing them together in one? I appreciate it very much!
June 21, 2012 at 2:32 pm
Yes, you need to look at the articles in my signature block regarding cross tabs. There are two and I recommend reading both. The second, if I remember correctly, talks about dynamic cross tabs.
June 21, 2012 at 2:57 pm
Will do. I appreciate your help very much. That is a cool piece of script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply