• Status: Solved
• Priority: Medium
• Security: Public
• Views: 323

# Subqueries with Aggregates and Constants

Problem:  Need to transform data from horizontal to vertical.  IE

Part           Date           Defect1     Defect2
123         5/1/2009           5               6

To

Part           Date            Defect       DefectTotal
123         5/1/2009       Defect1             5
123         5/1/2009       Defect2             6

I am pretty sure this needs to be done with a sub query and I am not sure how to create it.

I have attached a snip of code.  Can you tell me where to enter the subquery to Keep updated the Fields Defect and DefectTotal?

Thanks
``````Select 'ColdSpots' As Defect,Sum(ColdSpot) as DefectTotal,
From SCRAP
Where Dt between '5/1/2009' and '5/20/2009'
``````
0
Jlucht
• 3
• 2
1 Solution

Commented:
how many columns total do you have?  ie Defect1, Defect2, etc.
0

Author Commented:
15 defects in total
0

Commented:
easy way to do it in 2000 (2005 would be different)
declare @date1 datetime, @date2 datetime
select @date1 = '5/1/2009', @date2 = '5/20/2009'

select part, date, defect = 'Defect1' , defecttotal = Defect1
From SCRAP
Where Dt between @date1 and @date2
UNION ALL
select part, date, defect = 'Defect2' , defecttotal = Defect2
From SCRAP
Where Dt between @date1 and @date2

....et.al.
0

Author Commented:
That is the code that I need to do what I need.  One more question.  How would I insert that record set into a table.

Below is the code that I used to do it.  I had to change a couple of things.

Thanks ALOT!!!

Joe

``````
declare @date1 datetime, @date2 datetime
select @date1 = '5/1/2009', @date2 = '5/20/2009'

select Distinct partnum, defect = 'Holes' , defecttotal = Sum(Hole)
From SCRAP
Where Dt between @date1 and @date2
Group By Scrap.PartNum
UNION ALL
select Distinct partNum, defect = 'ColdSpot' , defecttotal = Sum(ColdSpot)
From SCRAP
Where Dt between @date1 and @date2
Group By Scrap.PartNum
``````
0

Author Commented:
Thanks for everything
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.