t-sql group by

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')

Open in new window


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?
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Here is a script:

declare @TestEmployees2 TABLE 
(
	[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
)

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

select [EmployeeID], [Region], [Company], [LastName], [FirstName], [MidInit], sum([HoursWorked]), [TookTimeOff]
from @TestEmployees2
group by [EmployeeID], [Region], [Company], [LastName], [FirstName], [MidInit], [TookTimeOff]
order by [LastName], [FirstName], [MidInit]

Open in new window


Let me first start with saying big THANK YOU for providing a script and a layout of an expected output.

Having said that...(don't you people ever get satisfied???)

1/ For requirements so small you may please start considering using a table variable. Benefit is expert may not wish to create such temp tables in any database of their own.

2/ You may also wish to start using a rather concise form of insert into when number of rows are less than 1000.

Anyway....

Was the tricky part of your question to suppress the Employee Id? If yes, if you plan to use SSRS it is easy to do it there.
Olaf DoschkeSoftware DeveloperCommented:
I agree with Nitin, and as I added a Count(*) to his query I realized your initial data already is aggregated. The only result row his query summarizes is the one about Michael J Houston, which is still two rows in your excel preview.

If it's all about the EmployeeID not displayed twice, that's not what you prepare in an SQL result, that's a reporting option to suppress repeated values, that's all there is to it.

Bye, Olaf.
Mark WillsTopic AdvisorCommented:
Well, I unconditionally appreciate the scripts to build and populate and the desired results.

Keep asking question this way, and you will always get a better outcome.

It would seem that some Experts are never satisfied :)

Have a look at
;with CTE_Employee as
(
   select [Region], [Company], cast(employeeid as varchar(10)) as EmployeeID, [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]
         ,row_number() over (partition by employeeid order by region,company,LastName,FirstName,HoursWorked) as rn
   from TestEmployees2
)  select case when rn = 1 then EmployeeID else '' end as EmployeeID
         ,[Region], [Company], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff] 
   from CTE_Employee
   order by [LastName],[FirstName], RN   

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
The non-cte approach would be create a sub-query as a derived table
select case when rn = 1 then EmployeeID else '' end as EmployeeID
      ,[Region], [Company], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff] 
from   ( select [Region], [Company], cast(employeeid as varchar(10)) as EmployeeID, [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]
               ,row_number() over (partition by employeeid order by region,company,LastName,FirstName,HoursWorked) as rn
         from TestEmployees2) as SDT
order by [LastName],[FirstName], RN

Open in new window

maqskywalkerAuthor Commented:
Thanks for your great info.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.