troubleshooting Question

SQL Group By Syntax SQL Server 2005

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server 2005SQL
9 Comments1 Solution254 ViewsLast Modified:
I am trying to rearrange this script so that it will have one column for each type of pay code per record. In the database each pay record is on a separate record. The original query did not include the group by and it works, however each record is on a separate line. It would be easier for the user if the 6 possible pay codes were on the same line. The end result I am looking for is:

NIGHT1,NIGHT1PAYRATE,NIGHT2,NIGHT2PAYRATE,VACATN,VACATNPAYRATE  ETC...

I keep getting a message about the payrolcd is not in the group by. I do not want the payrolcd in the group by. It is grouped by checkdate and employid.


select MAX(employid),MAX(chekdate),
case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end  as PAYCODE,
case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end  as NIGHT1HOURS,
case payrolcd WHEN 'NIGHT1' THEN payrate  else 0 end  as NIGHT1RATE,
case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end  as NIGHT1TOTAL,
case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end  as PAYCODE,
case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end  as NIGHT2HOURS,
case payrolcd WHEN 'NIGHT2' THEN payrate  else 0 end  as NIGHT2RATE,
case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end  as NIGHT2TOTAL,
case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end  as PAYCODE,
case payrolcd WHEN 'VACATN' THEN untstopy else 0 end  as VACATNHOURS,
case payrolcd WHEN 'VACATN' THEN payrate  else 0 end  as VACATNRATE,
case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end  as VACATNTOTAL,
case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end  as PAYCODE,
case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end  as SICKTMHOURS,
case payrolcd WHEN 'SICKTM' THEN payrate  else 0 end  as SICKTMRATE,
case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end  as SICKTMTOTAL,
case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end  as PAYCODE,
case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end  as HOLIDYHOURS,
case payrolcd WHEN 'HOLIDY' THEN payrate  else 0 end  as HOLIDYRATE,
case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end  as HOLIDYTOTAL,
case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end  as PAYCODE,
case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end  as PERSNLHOURS,
case payrolcd WHEN 'PERSNL' THEN payrate  else 0 end  as PERSNLRATE,
case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end  as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros