asked on

t-sql group by

I'm using sql server 2008.

I have a table that looks like this:

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:

What's the best way to get something like that?

SOLUTION

Nitin Sontakke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olaf Doschke

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.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

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

maqskywalker

ASKER

Thanks for your great info.