Help with Query. 1

[serino]

I am having trouble figuring out on how to accomplish this, I have a table with the below information. Is it possible to pull the HRS and AMT onto the same record for each SubNo? Of course, I would have to rename the fields for LOTCLEAN AS LHRS and LAMT and FINAL GRADE to FHRS and FAMT.

SubNo SubNME Zone PHSNME HRS AMT
394 sycamore park SE Rough Grade 2.2 350
394 sycamore park SE Lotclean 6 270
394 sycamore park SE Final Grade 1.8 86
3102 Star Valley SW Rough Grade 1.2 250
3102 Star Valley SW Lotclean 6 270
3102 Star Valley SW Final Grade 1.8 86


The Results would look like this and I would not need the PHSNME field.

SubNo SubNme Zone RHRS RAMT LHRS LAmount FHRS FAMT
394 sycamore park SE 2.2 350 6 270 1.8 86
3102 Star Valley SW 1.2 250 6 270 1.8 86

 

[Crowley16]

you could use subqueries, or join the table with itself...

subquery:

select
fld1,
fld2,
(select fld3 from table as t1 where table.Key = t1.key) as fldNme,
...
from
table



--------------------
Procrastinate Now!

 

[PHV]

Something like this ?
SELECT R.SubNo, R.SubNme, R.Zone, R.HRS AS RHRS, R.AMT AS RAMT, L.HRS AS LHRS, L.AMT AS LAmount, F.HRS AS FHRS, F.AMT AS FAMT
FROM (yourTable AS R
INNER JOIN yourTable AS L ON R.SubNo = L.SubNo AND R.SubNme = L.SubNme AND R.Zone = L.Zone)
INNER JOIN yourTable AS F ON R.SubNo = F.SubNo AND R.SubNme = F.SubNme AND R.Zone = F.Zone
WHERE R.PHSNME = 'Rough Grade'
AND L.PHSNME = 'Lotclean'
AND F.PHSNME = 'Final Grade'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

UNION queries

Select SubNo, SubNME, Zone, HRS, AMT, 0, 0, 0, 0
From YourTable
Where PHSNME='Rough Grade'
UNION
Select SubNo, SubNME, Zone, 0, 0, HRS, AMT, 0, 0
From YourTable
Where PHSNME='Lotclean'
UNION
...

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[serino]

Thank You all for your suggestions.

I tried PHV suggestion and worked out great. Thank you all again!