HowTo use PIVOT on Select in SQL Server 2005
-
I have an Application that Schedules Employee's Appointments in a Location with a Client for a Service of a particular Time.
The Appointments Table has a DateOnly Column and 2 TimeOnly Columns.
This is the Query :-
SELECT ServiceAppointments.TimeStart, Employees.KnownAsName, ServiceCubicles.ServiceCubicleName, Services.FullDescription, Clients.ClientName,
ServiceAppointments.TimeFinish
FROM ServiceAppointments INNER JOIN
Employees ON ServiceAppointments.EmployeeID = Employees.EmployeeID INNER JOIN
ServiceCubicles ON ServiceAppointments.ServiceCubicleID = ServiceCubicles.ServiceCubicleID INNER JOIN
Services ON ServiceAppointments.ServiceID = Services.ServiceID INNER JOIN
Clients ON ServiceAppointments.ClientID = Clients.ClientID
WHERE (ServiceAppointments.DateStart = '2001/3/5')
This returns :-
What I'm trying to achieve is to have the Columns showing as :-
TimeStart KnownAsName-1 KnownAsName-2 KnownAsName-3 etc.
with the Values of the remaining Columns concatenated (ServiceCubicles.ServiceCubicleName, Services.FullDescription, Clients.ClientName, ServiceAppointments.TimeFinish) under the related KnownAsName and against the TimeStart. Perhaps using a Pipe as a separator (+ '|' +)
Is this possible?
Regards, Graham.
-
Hi Graham,
I'll be glad to help you if you specify DDL, what you have coded so far and expected results.
Let me suggest you to take a look at this article to see how to post sample data:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Regards
Gianluca
-- Gianluca Sartori
-
I have an Application that Schedules Employee's Appointments in a Location with a Client for a Service of a particular Time.
The Appointments Table has a DateOnly Column (DateStart) and 2 TimeOnly Columns (TimeStart and TimeFinish). The other Columns are Char.
This is the Query :-
SELECT ServiceAppointments.TimeStart, Employees.KnownAsName, ServiceCubicles.ServiceCubicleName, Services.FullDescription, Clients.ClientName,
ServiceAppointments.TimeFinish
FROM ServiceAppointments INNER JOIN
Employees ON ServiceAppointments.EmployeeID = Employees.EmployeeID INNER JOIN
ServiceCubicles ON ServiceAppointments.ServiceCubicleID = ServiceCubicles.ServiceCubicleID INNER JOIN
Services ON ServiceAppointments.ServiceID = Services.ServiceID INNER JOIN
Clients ON ServiceAppointments.ClientID = Clients.ClientID
WHERE (ServiceAppointments.DateStart = '2001/3/5')
This returns :-
TimeStart KnownAsName ServiceCubicleName FullDescription ClientName TimeFinish
1/01/1900 8:30:00 PM Jacqui Cronulla Body Peel Irons Graham D. 1/01/1900 9:15:00 PM
1/01/1900 8:30:00 PM Jacqui Cronulla Feet Pedicure Irons Jacqui 1/01/1900 9:00:00 PM
1/01/1900 9:30:00 PM Jacqui Collaroy Full Facial Hahne Tanya 1/01/1900 10:30:00 PM
1/01/1900 10:40:00 PM Jacqui Collaroy Deep Cleanse Facial Hales Melanie 1/01/1900 11:25:00 PM
1/01/1900 11:30:00 PM Angela Collaroy Full Facial Irons Jacqui1/01/1900 12:30:00 AM
1/01/1900 11:30:00 PM Angela Collaroy Deep Cleanse Facial Irons Jacqui 1/01/1900 12:15:00 AM
What I'm trying to achieve is to have the Columns showing as :-
TimeStart Jacqui Angela OtherName?
1/01/1900 8:30:00 PM
1/01/1900 8:30:00 PM
1/01/1900 9:30:00 PM
1/01/1900 10:40:00 PM
1/01/1900 11:30:00 PM
1/01/1900 11:30:00 PM
with the Values of the remaining Columns concatenated (ServiceCubicles.ServiceCubicleName, Services.FullDescription, Clients.ClientName, ServiceAppointments.TimeFinish) under the related KnownAsName and against the TimeStart. Perhaps using a Pipe as a separator (+ '|' +)
Is this possible?
Regards, Graham.
-
Hi,
I hope the above is more understandable.
I've never done that before so it was a bit more of the learning curve.
Many thanks.
-
What I would have expected you to post was DDL for tables and sample data, something similar to this:
/*
* DDL FOR TABLES
*/
IF OBJECT_ID('Tempdb..#ServiceAppointments') IS NOT NULL DROP TABLE #ServiceAppointments
IF OBJECT_ID('Tempdb..#Employees') IS NOT NULL DROP TABLE #Employees
IF OBJECT_ID('Tempdb..#ServiceCubicles') IS NOT NULL DROP TABLE #ServiceCubicles
IF OBJECT_ID('Tempdb..#Services') IS NOT NULL DROP TABLE #Services
IF OBJECT_ID('Tempdb..#Clients') IS NOT NULL DROP TABLE #Clients
CREATE TABLE #ServiceAppointments (
AppointmentId int,
DateStart datetime,
TimeStart datetime,
TimeFinish datetime,
EmployeeId int,
ServiceCubicleId int,
ServiceId int,
ClientId int
)
CREATE TABLE #Employees (
EmployeeId int,
KnownAsName varchar(50),
)
CREATE TABLE #ServiceCubicles (
ServiceCubicleID int,
ServiceCubicleName varchar(50)
)
CREATE TABLE #Services (
ServiceId int,
FullDescription varchar(50)
)
CREATE TABLE #Clients (
ClientId int,
ClientName varchar(50)
)
/*
* SAMPLE DATA
*/
INSERT INTO #Employees VALUES (1,'Jacqui')
INSERT INTO #Employees VALUES (2,'Angela')
INSERT INTO #Services VALUES (1, 'Body Peel')
INSERT INTO #Services VALUES (2, 'Feet Pedicure')
INSERT INTO #Services VALUES (3, 'Full Facial')
INSERT INTO #Services VALUES (4, 'Deep Cleanse Facial')
INSERT INTO #Clients VALUES (1, 'Irons Graham D.')
INSERT INTO #Clients VALUES (2, 'Irons Jacqui')
INSERT INTO #Clients VALUES (3, 'Hahne Tanya')
INSERT INTO #Clients VALUES (4, 'Hales Melanie')
INSERT INTO #ServiceCubicles VALUES (1, 'Cronulla')
INSERT INTO #ServiceCubicles VALUES (2, 'Collaroy')
INSERT INTO #ServiceAppointments VALUES (1, '2001-03-05', '1/01/1900 8:30:00 PM', '1/01/1900 9:15:00 PM', 1, 1, 1, 1)
INSERT INTO #ServiceAppointments VALUES (2, '2001-03-05', '1/01/1900 8:30:00 PM', '1/01/1900 9:00:00 PM', 1, 1, 2, 2)
INSERT INTO #ServiceAppointments VALUES (3, '2001-03-05', '1/01/1900 9:30:00 PM', '1/01/1900 10:30:00 PM', 1, 2, 3, 3)
INSERT INTO #ServiceAppointments VALUES (4, '2001-03-05', '1/01/1900 10:40:00 PM', '1/01/1900 11:25:00 PM', 1, 2, 4, 4)
INSERT INTO #ServiceAppointments VALUES (5, '2001-03-05', '1/01/1900 11:30:00 PM', '1/01/1900 12:30:00 AM', 2, 2, 3, 2)
INSERT INTO #ServiceAppointments VALUES (6, '2001-03-05', '1/01/1900 11:30:00 PM', '1/01/1900 12:15:00 AM', 2, 2, 4, 2)
Let me work around it a bit and I'll be back with some tip.
Regards
Gianluca
-- Gianluca Sartori
-
This should do the trick:
WITH CTE (TimeStart, KnownAsName, ServiceDetails)
AS (
SELECT ServiceAppointments.TimeStart,
Employees.KnownAsName,
ServiceCubicles.ServiceCubicleName + '|' +
Services.FullDescription + '|' +
Clients.ClientName + '|' +
CONVERT(varchar(50),ServiceAppointments.TimeFinish,108) AS ServiceDetails
FROM #ServiceAppointments AS ServiceAppointments
INNER JOIN #Employees AS Employees
ON ServiceAppointments.EmployeeID = Employees.EmployeeID
INNER JOIN #ServiceCubicles AS ServiceCubicles
ON ServiceAppointments.ServiceCubicleID = ServiceCubicles.ServiceCubicleID
INNER JOIN #Services AS Services
ON ServiceAppointments.ServiceID = Services.ServiceID
INNER JOIN #Clients AS Clients
ON ServiceAppointments.ClientID = Clients.ClientID
WHERE ( ServiceAppointments.DateStart = '2001/3/5' )
)
SELECT TimeStart, [Jacqui], [Angela]
FROM (
SELECT *
FROM CTE
) AS src
PIVOT( MIN(ServiceDetails) FOR KnownAsName IN ([Jacqui], [Angela])) as pvt
Is this what you are trying to achieve?
-- Gianluca Sartori
-
Hi Gianluca,
I thought this was looking good. It Parsed OK.
But I get the following error message when I execute it.
Msg 208, Level 16, State 0, Line 1Invalid object name '#ServiceAppointments'.
I've not seen the use of # before.
Regards, Graham.
-
Hi Gianluca,
I changed the FROM Clause back to how I had it above and Bingo!
That's what I'm after.
However, there may be any number of Employees. Would the solution to this be to do a SELECT on Employees first and then Loop through that result in some way?
Time Start Jacqui Angela
1900-01-01 20:30:00.000 Cronulla|Body Peel|Irons Graham D.|21:15:00 NULL
1900-01-01 21:30:00.000 Collaroy|Full Facial|Hahne Tanya|22:30:00 NULL
1900-01-01 22:40:00.000 Collaroy|Deep Cleanse Facial|Hales Melanie|23:25:00 NULL
1900-01-01 23:30:00.000 NULL Collaroy|Deep Cleanse Facial|Irons Jacqui|00:15:00
Regards, Graham.
-
Graham.Irons (7/15/2009)
I've not seen the use of # before.# denotes a temporary table, which I created in the DDL and test data script I posted earlier.
Temporary tables are visible only to the session (connection) they're created in and are deleted when the session is closed.
I used temp tables in the script just because I didn't want to create tables in my test db. To revert the query back to your db, get rid of the #.
-- Gianluca Sartori
-
Graham.Irons (7/15/2009)
However, there may be any number of Employees.This is one of the limitations of the PIVOT operator: it must have fixed pivoting values.
In your case you may consider using dynamic sql created on the fly to include all the values to pivot for. If you are building the sql query from C# or any other programming language, you could loop through the employees to build the pivoting values string to add in the query. If you are planning to put this in a stored procedure, you could do the same thing in T-SQL and then execute the query with EXEC or sp_executesql. See BOL for more information.
Hope this helps
Gianluca
-- Gianluca Sartori
-
Hi Gianluca,
The Application is in VB.Net. I'm upgrading something I did some years ago and now using SQL Server 2005 instead of MS Access.
I was planning on a Stored Procedure so I'll lookup on T-SQL and build my string as you suggest.
I would like to thank you very much for the assistance you have given me. The result will give me exactly what I was after. It's a beautiful thing.
Best Regards,
Graham.
-
Hi Gianluca,
Well, I started with the Stored Proc idea. I thought I will have a Query to return the KnownAsNames and build a string to pass to the Stored Proc. as a Param. However the stored proc returns an error.
CREATE PROCEDURE sp_GetAppointmentsByDate
-- Add the parameters for the stored procedure here
@Therapists VarChar(50) = '[]',
@Date DateOnly = '1/1/1900'
AS
Msg 102, Level 15, State 1, Procedure sp_GetAppointmentsByDate, Line 35
Incorrect syntax near '@Therapists'
I believe the error is because I have declared the Param @Therapists as VarChar(50) when it is expecting Columns inside square brackets.
I tried Column and that also caused an error.
Do you have any suggestions for me please?
Regards, Graham.
-
You can't use parameters declared outside the dynamic statement in the statement itself. You have to declare the params in the appropriate argument of sp_executesql. Example:
declare @sql nvarchar(4000)
declare @value nvarchar(50)
set @value = 'somevalue'
set @sql = 'select somecolumn from sometable where somecolumn = @criteria'
exec sp_executesql @sql, N'@criteria nvarchar(50)', @value
Hope this helps
Gianluca
-- Gianluca Sartori
-
I'll give it a try.
Thank you very much.
Regards, Graham.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply