May 27, 2015 at 8:51 am
As many articles I read about PIVOT, I still don't get it. i can do it this way but wanted to try pivot:
SELECT job_name, STATUS, COUNT(*)
FROM vw_SSIS_Job_Status
GROUP BY JOB_NAME, STATUS
I have this table:
CREATE TABLE [dbo].[SSIS_Job_Status](
[Job_Status_ID] [int] IDENTITY(1,1) NOT NULL,
[Job_id] [int] NOT NULL,
[etlkey] [varchar](60) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Status] [varchar](10) NULL,
[DataLastUpdateDate] [datetime] NULL,
CONSTRAINT [PK_SSIS_Job_Status] PRIMARY KEY CLUSTERED
(
[Job_Status_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I want to pivot the table data so that the counts of each job's success or falure are in separate columns. I'm sure I can do this another way but wanted to see if I could use Pivot. any help is appreciated.
JOB_NAMESUCCESSFAILURE
TEST15532
TEST2 725[/size]
May 27, 2015 at 9:23 am
There's a couple of choices to do this. I prefer to use cross tabs, while other prefer to use the pivot operator.
Cross-tabs allow more flexibility and might outperform pivot in most cases.
You can read more about these techniques in here: http://www.sqlservercentral.com/articles/T-SQL/63681/
Here's an example for you.
INSERT INTO SSIS_Job_Status(Job_id, etlkey, StartDate, EndDate, [Status], [DataLastUpdateDate])
VALUES(1, 'TEST1', GETDATE() - 10, GETDATE() - 9, 'Success', GETDATE()),
(1, 'TEST1', GETDATE() - 8, GETDATE() - 7, 'Success', GETDATE()),
(1, 'TEST1', GETDATE() - 6, GETDATE() - 5, 'Failure', GETDATE()),
(2, 'TEST2', GETDATE() - 4, GETDATE() - 3, 'Success', GETDATE()),
(2, 'TEST2', GETDATE() - 2, GETDATE() - 1, 'Success', GETDATE())
GO
--Cross tabs
SELECT etlkey AS job_name,
SUM(CASE WHEN [Status] = 'Success' THEN 1 ELSE 0 END) AS Success,
SUM(CASE WHEN [Status] = 'Failure' THEN 1 ELSE 0 END) AS Failure
FROM SSIS_Job_Status
GROUP BY etlkey;
--Pre-aggregated cross tabs
SELECT etlkey AS job_name,
SUM(CASE WHEN [Status] = 'Success' THEN StatusCount ELSE 0 END) AS Success,
SUM(CASE WHEN [Status] = 'Failure' THEN StatusCount ELSE 0 END) AS Failure
FROM (SELECT etlkey, [Status], COUNT(*) StatusCount
FROM SSIS_Job_Status
GROUP BY etlkey, [Status]) x
GROUP BY etlkey;
--Pivot
SELECT etlkey AS job_name,
Success,
Failure
FROM (SELECT etlkey, [Status] FROM SSIS_Job_Status) AS s
PIVOT (COUNT(Status) FOR [Status] IN ([Success], [Failure])) AS p;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply