Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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