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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.