May 7, 2009 at 9:23 pm
Hi,
I'm trying to get a simple query to work that contains the PIVOT operator. I've read the documentation here, but I can't seem to get my query to work 🙁
This is what I have so far...
SELECT UserID, [FollowUpCall] AS FollowUpCall2, [HealthScore] AS HealthScore2
FROM (SELECT UserID, AssessmentDate, FollowUpCall, HealthScore
FROM tblAssessments
WHERE UserID = '93') AS qryAlias PIVOT (MAX(FollowUpCall) FOR [AssessmentDate] IN ([FollowUpCall], [HealthScore])) AS pvtTableAlias;
Essentially, I would like the data returned to have the AssessmentDate as the column header, and the columns FollowUpCall, HealthScore as the rows.
I'd love some help if anybody is willing.
May 7, 2009 at 9:38 pm
Hi,
Show some sample data with desired output.
ARUN SAS
May 7, 2009 at 10:09 pm
Thanks for your reply. I have a single table:
UserID int
AssessmentDate datetime
FollowUpCall int
HealthScore int
The data is only displaying data for a single UserID.
I would like the data to be displayed as:
12-May-2009 14-May-2009
FollowUpCall 1 0
Health Score 5 25
formatting is no good, sorry. Basically 3 columns, first column with column names, and the following columns with AssessmentDate as the header.
Each row should be a normal column.
May 7, 2009 at 10:27 pm
You know, the people that help out here are all volunteers. Providing the CREATE TABLE scripts for the tables affected, and INSERT statements to put some test data into those tables will go a long way in getting people to look at your issue and help you out. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 7, 2009 at 10:32 pm
WayneS (5/7/2009)
You know, the people that help out here are all volunteers. Providing the CREATE TABLE scripts for the tables affected, and INSERT statements to put some test data into those tables will go a long way in getting people to look at your issue and help you out. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.
Apoligies Wayne, I will do as you suggest. I will create the script etc from SSMS and post back here.
May 7, 2009 at 10:38 pm
The following three queries should give some structure and data. Any help would be much appreciated.
TABLE
CREATE TABLE [dbo].[tblAssessments](
[AssessmentID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[AssessmentDate] [datetime] NULL,
[FollowUpCall] [numeric](1, 0) NULL,
[HealthScore] [int] NULL,
CONSTRAINT [PK_tblAssessments] PRIMARY KEY CLUSTERED
(
[AssessmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DATA
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('1', '04-May-2009', '1', '5');
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('1', '06-May-2009', '0', '25');
May 8, 2009 at 12:38 am
Hi,
try this
CREATE TABLE #tblAssessments
(
AssessmentID int IDENTITY(1,1) NOT NULL,
UserID int NULL,
AssessmentDate datetime NULL,
FollowUpCall numeric(1, 0) NULL,
HealthScore int NULL
)
insert into #tblAssessments (UserID,AssessmentDate,FollowUpCall,HealthScore)
values (1, '04-May-2009', 1, 5)
insert into #tblAssessments (UserID,AssessmentDate,FollowUpCall,HealthScore)
values (1, '06-May-2009', 0, 25)
select a.UserID,b.colname,
max(case when b.slno = 1 and a.AssessmentDate = '2009-05-04'then a.followUpCall
when b.slno = 2 and a.AssessmentDate = '2009-05-04'then a.HealthScore else 0 end)[2009-05-04] ,
max(case when b.slno = 1 and a.AssessmentDate = '2009-05-06'then a.followUpCall
when b.slno = 2 and a.AssessmentDate = '2009-05-06'then a.HealthScore else 0 end)[2009-05-06]
from #tblAssessments a
cross join (select 'FollowUpCall'as colname,1 as slno union select 'HealthScore',2)as b
group by a.UserID,b.colname
RESULT
UserIDcolname2009-05-042009-05-06
1FollowUpCall10
1HealthScore525
ARUN SAS
May 8, 2009 at 2:51 am
Assuming that the no. of pivot columns is not static, you have to build a dynamic pivot SQL for each distinct value of the pivot column.
The below solution uses the SQL 2000 method of pivoting columns using CASE statements as it performs better than the SQL 2005 PIVOT method. And also, the solution uses UNPIVOT to convert the columns (FollowUpCall, HealthScore) to rows.
DECLARE @PivotedColumnSQL VARCHAR(MAX),
@sql NVARCHAR(MAX)
IF ( OBJECT_ID( 'tempdb..#tblAssessments' ) IS NOT NULL )
DROP TABLE #tblAssessments
CREATE TABLE #tblAssessments
(
AssessmentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserID INT NULL,
AssessmentDate DATETIME NULL,
FollowUpCall NUMERIC(1, 0) NULL,
HealthScore INT NULL
)
INSERT #tblAssessments( UserID, AssessmentDate, FollowUpCall, HealthScore )
SELECT 1, '04-May-2009', 1, 5
UNION ALL
SELECT 1, '06-May-2009', 0, 25
UNION ALL
SELECT 2, '06-Jan-2009', 5, 25
SELECT @PivotedColumnSQL = COALESCE( @PivotedColumnSQL + ', ', '' )
+ 'SUM( ( CASE WHEN DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) = ''' + CONVERT( VARCHAR(10), AssessmentDate, 112 ) + ''' THEN UnPivotValue ELSE 0 END ) ) AS [' + CONVERT( VARCHAR(11), AssessmentDate, 106 ) + ']'
FROM ( SELECT DISTINCT DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) AS AssessmentDate FROM #tblAssessments ) A
ORDER BY AssessmentDate
SELECT @sql = 'SELECT UserID, UnPivotColumn ' + COALESCE( ', ' + @PivotedColumnSQL, '' ) + ' '
+ 'FROM ( '
+ ' SELECTUserID, AssessmentDate, UnPivotColumn, UnPivotValue '
+ ' FROM ( '
+ ' SELECT UserID, AssessmentDate, '
+ ' CONVERT( INT, FollowUpCall ) AS FollowUpCall, HealthScore '
+ ' FROM #tblAssessments '
+ ' ) A '
+ ' UNPIVOT '
+ ' ( '
+ ' UnPivotValue FOR UnPivotColumn IN( FollowUpCall, HealthScore ) '
+ ' ) UP '
+ ' ) A '
+ 'GROUP BY UserID, UnPivotColumn '
+ 'ORDER BY UserID, UnPivotColumn '
--PRINT @PivotedColumnSQL
--PRINT @sql
EXECUTE( @sql )
For more information on how it works, see the following links
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/cross+tab/65048/
--Ramesh
May 8, 2009 at 3:07 am
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Failing to plan is Planning to fail
May 11, 2009 at 9:31 pm
Thanks Ramesh, your solution works for the most part. I have taken your code, and am trying to apply the WHERE clause as I need to only show data for particular AssessmentID's based on a single UserID. I can get the User ID to work fine by using the following:
DECLARE @PivotedColumnSQL VARCHAR(MAX),
@sql NVARCHAR(MAX)
SELECT @PivotedColumnSQL = COALESCE( @PivotedColumnSQL + ', ', '' )
+ 'SUM( ( CASE WHEN DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) = ''' + CONVERT( VARCHAR(10), AssessmentDate, 112 ) + ''' THEN UnPivotValue ELSE 0 END ) ) AS [' + CONVERT( VARCHAR(11), AssessmentDate, 106 ) + ']'
FROM ( SELECT DISTINCT DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) AS AssessmentDate FROM tblAssessments ) A
ORDER BY AssessmentDate
SELECT @sql = 'SELECT UserID, UnPivotColumn ' + COALESCE( ', ' + @PivotedColumnSQL, '' ) + ' '
+ 'FROM ( '
+ ' SELECT AssessmentID, UserID, AssessmentDate, UnPivotColumn, UnPivotValue '
+ ' FROM ( '
+ ' SELECT AssessmentID, UserID, AssessmentDate, '
+ ' CONVERT( INT, FollowUpCall ) AS FollowUpCall, HealthScore '
+ ' FROM tblAssessments '
+ 'WHERE UserID=93'
+ ' ) A '
+ ' UNPIVOT '
+ ' ( '
+ ' UnPivotValue FOR UnPivotColumn IN( FollowUpCall, HealthScore ) '
+ ' ) UP '
+ ' ) A '
+ 'GROUP BY UserID, UnPivotColumn '
+ 'ORDER BY UserID, UnPivotColumn '
--PRINT @PivotedColumnSQL
--PRINT @sql
EXECUTE( @sql )
However, I do not want to retrieve all records for the user in question. For example, UserID=93 has 3 records, but the user only wants to see two of these records in this case, but like to see 3 next time. The SQL query needs to be able to handle multiple AssessmentID's and variables numbers of them.
I can't get that to work properly - it seems to return all records for the selected UserID all the time regardless of the AssessmentID's specified in the where clause. eg:
WHERE (AssessmentID=1 OR AssessmentID=2) AND UserID=93
I am using the following data:
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('93'
,'15/04/2009 12:30:00 PM'
,'1'
,'3');
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('93'
,'15/04/2008 12:30:00 PM'
,'1'
,'10');
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('93'
,'15/04/2007 12:30:00 PM'
,'0'
,'7');
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('94'
,'15/04/2008 12:30:00 PM'
,'1'
,'5');
INSERT INTO [dbo].[tblAssessments]
([UserID]
,[AssessmentDate]
,[FollowUpCall]
,[HealthScore])
VALUES
('94'
,'15/04/2007 12:30:00 PM'
,'1'
,'6');
Any help would be much appreciated. I've tried a number of ways of getting the query to work but I just can't seem to get it.
May 12, 2009 at 5:47 am
WHERE Clause should be applied to the inner derived query of the dynamic select.
DECLARE @PivotedColumnSQL VARCHAR(MAX),
@sql NVARCHAR(MAX),
@WhereClause VARCHAR(1000)
IF ( OBJECT_ID( 'tempdb..#tblAssessments' ) IS NOT NULL )
DROP TABLE #tblAssessments
CREATE TABLE #tblAssessments
(
AssessmentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserID INT NULL,
AssessmentDate DATETIME NULL,
FollowUpCall NUMERIC(1, 0) NULL,
HealthScore INT NULL
)
-- Set the where clause
SELECT@WhereClause = '( AssessmentID = 1 OR AssessmentID = 2 ) AND UserID = 93'
INSERT #tblAssessments( UserID, AssessmentDate, FollowUpCall, HealthScore )
SELECT93, '15-Apr-2009 12:30:00 PM', 1, 3
UNION ALL
SELECT93, '15-Apr-2008 12:30:00 PM', 1, 10
UNION ALL
SELECT93, '15-Apr-2007 12:30:00 PM', 0, 7
UNION ALL
SELECT94, '15-Apr-2008 12:30:00 PM', 1, 5
UNION ALL
SELECT94, '15-Apr-2007 12:30:00 PM', 1, 6
SELECT @PivotedColumnSQL = COALESCE( @PivotedColumnSQL + ', ', '' )
+ 'SUM( ( CASE WHEN DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) = ''' + CONVERT( VARCHAR(10), AssessmentDate, 112 ) + ''' THEN UnPivotValue ELSE 0 END ) ) AS [' + CONVERT( VARCHAR(11), AssessmentDate, 106 ) + ']'
FROM ( SELECT DISTINCT DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) AS AssessmentDate FROM #tblAssessments ) A
ORDER BY AssessmentDate
SELECT @sql = 'SELECT UserID, UnPivotColumn ' + COALESCE( ', ' + @PivotedColumnSQL, '' ) + ' '
+ 'FROM ( '
+ ' SELECT UserID, AssessmentDate, UnPivotColumn, UnPivotValue '
+ ' FROM ( '
+ ' SELECT UserID, AssessmentDate, '
+ ' CONVERT( INT, FollowUpCall ) AS FollowUpCall, HealthScore '
+ ' FROM #tblAssessments '
+ ISNULL( ' WHERE ' + @WhereClause, '' ) -- Applying the where clause
+ ' ) A '
+ ' UNPIVOT '
+ ' ( '
+ ' UnPivotValue FOR UnPivotColumn IN( FollowUpCall, HealthScore ) '
+ ' ) UP '
+ ' ) A '
+ 'GROUP BY UserID, UnPivotColumn '
+ 'ORDER BY UserID, UnPivotColumn '
--PRINT @PivotedColumnSQL
--PRINT @sql
EXECUTE( @sql )
--Ramesh
May 13, 2009 at 8:27 pm
Thanks Ramesh. The data being returned is still not correct. I've attatched an image to show the data being returned and an edited image to show what it should be.
May 14, 2009 at 1:40 am
WHERE Clause needs to be added to the PivotedColumnSQL as well.
Revised Code:
DECLARE @PivotedColumnSQL VARCHAR(MAX),
@sql NVARCHAR(MAX),
@WhereClause VARCHAR(1000)
IF ( OBJECT_ID( 'tempdb..#tblAssessments' ) IS NOT NULL )
DROP TABLE #tblAssessments
CREATE TABLE #tblAssessments
(
AssessmentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserID INT NULL,
AssessmentDate DATETIME NULL,
FollowUpCall NUMERIC(1, 0) NULL,
HealthScore INT NULL
)
-- Set the where clause
SELECT @WhereClause = '( AssessmentID = 1 OR AssessmentID = 2 ) AND UserID = 93'
INSERT #tblAssessments( UserID, AssessmentDate, FollowUpCall, HealthScore )
SELECT 93, '15-Apr-2009 12:30:00 PM', 1, 3
UNION ALL
SELECT 93, '15-Apr-2008 12:30:00 PM', 1, 10
UNION ALL
SELECT 93, '15-Apr-2007 12:30:00 PM', 0, 7
UNION ALL
SELECT 94, '15-Apr-2008 12:30:00 PM', 1, 5
UNION ALL
SELECT 94, '15-Apr-2007 12:30:00 PM', 1, 6
SELECT@sql = 'SELECT@PivotedColumnSQL = COALESCE( @PivotedColumnSQL + '', '', '''' ) '
+ '+ ''SUM( ( CASE WHEN DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) = '''''' + CONVERT( VARCHAR(10), AssessmentDate, 112 ) + '''''' THEN UnPivotValue ELSE 0 END ) ) AS ['' + CONVERT( VARCHAR(11), AssessmentDate, 106 ) + ''] '''
+ 'FROM ( '
+ 'SELECTDISTINCT DATEADD( DAY, DATEDIFF( DAY, 0, AssessmentDate ), 0 ) AS AssessmentDate '
+ 'FROM#tblAssessments '
+ ISNULL( ' WHERE ' + @WhereClause, '' ) -- Applying the where clause
+ ') A '
+ 'ORDER BY AssessmentDate'
--PRINT @sql
EXECUTE sp_executesql @sql, N'@PivotedColumnSQL VARCHAR(MAX) OUTPUT', @PivotedColumnSQL = @PivotedColumnSQL OUTPUT
SELECT @sql = 'SELECT UserID, UnPivotColumn ' + COALESCE( ', ' + @PivotedColumnSQL, '' ) + ' '
+ 'FROM ( '
+ ' SELECT UserID, AssessmentDate, UnPivotColumn, UnPivotValue '
+ ' FROM ( '
+ ' SELECT UserID, AssessmentDate, '
+ ' CONVERT( INT, FollowUpCall ) AS FollowUpCall, HealthScore '
+ ' FROM #tblAssessments '
+ ISNULL( ' WHERE ' + @WhereClause, '' ) -- Applying the where clause
+ ' ) A '
+ ' UNPIVOT '
+ ' ( '
+ ' UnPivotValue FOR UnPivotColumn IN( FollowUpCall, HealthScore ) '
+ ' ) UP '
+ ' ) A '
+ 'GROUP BY UserID, UnPivotColumn '
+ 'ORDER BY UserID, UnPivotColumn '
--PRINT @PivotedColumnSQL
--PRINT @sql
EXECUTE( @sql )
--Ramesh
May 14, 2009 at 6:44 am
Ramesh, you are the greatest!
I appreciate all your help, thank you 😀
May 14, 2009 at 9:26 am
You are welcome, and thank you for the compliment.
--Ramesh
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply