troubleshooting Question

t-sql group by

Avatar of maqskywalker
maqskywalker asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
5 Comments2 Solutions235 ViewsLast Modified:
I'm using sql server 2008.

I have a table that looks like this:

table
This is script to create the table:
USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestEmployees2](
	[Region] [int] NULL,
	[Company] [nvarchar](255) NULL,
	[EmployeeID] [int] NULL,
	[LastName] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[MidInit] [nvarchar](255) NULL,
	[HoursWorked] [float] NULL,
	[TookTimeOff] [nvarchar](255) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522112, N'Smith', N'Elizabeth', N'A', 177, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522112, N'Smith', N'Elizabeth', N'A', 238, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (2, N'Walmart', 4523122, N'Johnson', N'Jim', N'R', 244.5, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (7, N'UPS', 4523122, N'Johnson', N'Jim', N'R', 188.1965, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522314, N'Miller', N'Sarah', N'M', 164, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522314, N'Miller', N'Sarah', N'M', 280, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522351, N'Kross', N'Chris', N'R', 513, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (9, N'Kmart', 4522351, N'Kross', N'Chris', N'R', 30, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522455, N'Flav', N'Flavor', N'P', 211, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4522455, N'Flav', N'Flavor', N'P', 275, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4527452, N'Foster', N'Jill', N'R', 105, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4527452, N'Foster', N'Jill', N'R', 176, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4537454, N'Crocket', N'Davey', N'L', 90, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (5, N'Acme Inc', 4537454, N'Crocket', N'Davey', N'L', 358, N'Yes')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (8, N'Target', 4539463, N'Houston', N'Michael', N'J', 80, N'No')
INSERT [dbo].[TestEmployees2] ([Region], [Company], [EmployeeID], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]) VALUES (8, N'Target', 4539463, N'Houston', N'Michael', N'J', 232, N'No')

Order By LastName,FirstName
Then Group the records by EmployeeID.

 I created this drawing in excel. This is what I want my result to look like:

desired query result
What's the best way to get something like that?
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 2 Answers and 5 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 2 Answers and 5 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