April 6, 2015 at 5:00 am
Hi..
Is there a way to hide all those columns with all zero values(zero value for all rows) in a dynamic pivot query?
Sunitha
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 6, 2015 at 7:02 am
sunitkrishna (4/6/2015)
Hi..Is there a way to hide all those columns with all zero values(zero value for all rows) in a dynamic pivot query?
Sunitha
Sure with a WHERE predicate? Or maybe a HAVING? Since you didn't actually provide ANY details that is about the best I can do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 6, 2015 at 7:46 am
Sean Lange (4/6/2015)
sunitkrishna (4/6/2015)
Hi..Is there a way to hide all those columns with all zero values(zero value for all rows) in a dynamic pivot query?
Sunitha
Sure with a WHERE predicate? Or maybe a HAVING? Since you didn't actually provide ANY details that is about the best I can do.
Sounds reasonable to me. Use a WHERE clause to filter rows.
April 7, 2015 at 12:07 am
Hi
Sorry for not adding the sample query.
Here it is:
-------------------------------------------------------------------------------------------------------------------
DECLARE @RunQry NVARCHAR(MAX),@DesignationNames NVARCHAR(MAX),@Designation NVARCHAR(MAX),@DesignationTotal nvarchar(MAX)
SELECT @Designation=COALESCE(@Designation+',','') + QUOTENAME([DesignationID])
FROM [dbo].[Designation] WHERE Status=0
SELECT @DesignationTotal=COALESCE(@DesignationTotal+'+','') + 'SUM(ISNULL(' + QUOTENAME([DesignationID]) + ',''0''))'
FROM [dbo].[Designation] WHERE Status=0--added by sunitha on 04/7/14 to get rowwise total
SELECT @DesignationNames=COALESCE(@DesignationNames+',','') + 'SUM(' + QUOTENAME([DesignationID]) + ')' + ' AS ' + QUOTENAME([DesignationName]) FROM [dbo] .[Designation] WHERE Status=0
SET @RunQry='SELECT ISNULL((select [ProjectName] from [dbo].[Projects] P WHERE P.ProjectId=ISNULL(PVT.[ProjectId],''Total'') AND P.Status=0),''_Total'') AS [Project],
' + @DesignationTotal + ' Total,
' + @DesignationNames + '
FROM
(
SELECT PA.EmpID,P.ProjectId,Designation,P.ProjectName
FROM [dbo].ProjectAssignment PA
INNER JOIN [dbo].Projects P on P.ProjectId=PA.Project AND P.SBUCode=''ITS''
left join [dbo].CurrentJobHistory CJH ON cjh.EmpID=PA.EmpID AND CJH.Location=PA.Location
where PA.[IsCurrent]=1 AND CJH.STATUS=0 AND PA.Status=0 AND P.STATUS=0
UNION
SELECT NULL AS EmpId,P.ProjectId,NULL AS Designation,P.ProjectName
FROM [dbo].[Projects]P where P.Status=0 AND P.SBUCode=''ITS'' AND P.ProjectID NOT IN
(SELECT [ProjectID] FROM [dbo].ProjectAssignment PA
INNER JOIN [dbo].Projects P on P.ProjectId=PA.Project
left join [dbo].CurrentJobHistory CJH ON cjh.EmpID=PA.EmpID AND CJH.Location=PA.Location
where PA.[IsCurrent]=1 AND CJH.STATUS=0 AND PA.Status=0 AND P.STATUS=0
)
) A
PIVOT
(
COUNT(EmpID)
FOR Designation IN (' + @Designation +')
) AS PVT Group By ProjectId WITH ROLLUP order by Project'--DESC'
EXEC(@RunQry)
--------------------------------------------------------------------------------------------------------------------
In my result set a few designations don't have values for any of the projects(These designations are not the same always).Currently I have replaced NULL with '0',but what I need is the removal of the entire column.
Where do I add the 'WHERE' clause?Pease help!!!
Sunitha Manoj
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 7, 2015 at 7:02 am
sunitkrishna (4/7/2015)
...In my result set a few designations don't have values for any of the projects(These designations are not the same always).Currently I have replaced NULL with '0',but what I need is the removal of the entire column.
Where do I add the 'WHERE' clause?Pease help!!!
Sunitha Manoj
Can you post ddl and sample data along with the desired output based on your sample data? You have almost 800 points at the time of your posting so this isn't exactly news that we need something to work with.
Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2015 at 2:06 am
Creating designation table
---------------------------
CREATE TABLE [dbo].[Designation](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[DesignationID] AS ('D'+CONVERT([nvarchar](40),[RecID])),
[DesignationName] [nvarchar](100) NULL,
[Description] [nvarchar](500) NULL,
[Status] [smallint] NULL,
[JobLevel] [nvarchar](10) NULL
) ON [PRIMARY]
sample data
-------------
INSERT INTO dbo.Designation
select 'Group Test Manager','Group Test Manager',0,9
INSERT INTO dbo.Designation
select 'SDM','SDM',0,10
INSERT INTO dbo.Designation
select 'Test Consultant','Test Consultant',0,8
creating Projects table
------------------------
CREATE TABLE [dbo].[Projects](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[ProjectID] AS ('P'+CONVERT([nvarchar](40),[RecID])),
[ProjectName] [nvarchar](100) NULL,
[Description] [nvarchar](500) NULL,
[Status] [smallint] NULL,
[ERPCode] [nvarchar](50) NULL,
[Account] [nvarchar](100) NULL,
[ReportingManager] [nvarchar](500) NULL,
[SBUCode] [nvarchar](50) NULL,
[CCCode] [nvarchar](50) NULL,
[ERPName] [nvarchar](100) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Projects] ADD CONSTRAINT [DF__Projects__Status__300424B4] DEFAULT ((0)) FOR [Status]
GO
sample data
-------------
insert into dbo.Projects
select 'CWT','CWT',0,'01012','','','','','CWT'
insert into dbo.Projects
select 'Infini','Infini',0,'01013','','','','','Infini'
insert into dbo.Projects
select 'JIT','JIT',0,'01014','','','','','JIT'
Create ProjectAssignment table
----------------------------------
CREATE TABLE [dbo].[ProjectAssignment](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[EmpID] [nvarchar](50) NULL,
[ProjectAssignmentDate] [datetime] NULL,
[Project] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[Overseas] [bit] NULL,
[Status] [tinyint] NULL,
[IsCurrent] [tinyint] NULL,
[Deallocationdate] [datetime] NULL,
[DeallocatedBy] [nvarchar](50) NULL,
[DeallocatedOn] [datetime] NULL,
[RoleInProject] [nvarchar](50) NULL,
[ApprovalStatus] [tinyint] NULL,
[ApprovedBy] [nvarchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProjectAssignment] ADD DEFAULT ((0)) FOR [Overseas]
GO
ALTER TABLE [dbo].[ProjectAssignment] ADD DEFAULT ((0)) FOR [Status]
GO
sample data
-----------
insert into dbo.ProjectAssignment
select 'A-6140','2014/03/04','P1','L30025',0,0,1,'','','',3,''
insert into dbo.ProjectAssignment
select 'A-6141','2014/03/04','P1','L30025',0,0,1,'','','',3,''
insert into dbo.ProjectAssignment
select 'A-6142','2014/03/04','P2','L30025',0,0,1,'','','',3,''
Creating CurrentJobHistory table
--------------------------------
CREATE TABLE [dbo].[CurrentJobHistory](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[EmpID] [nvarchar](50) NULL,
[Designation] [nvarchar](50) NULL,
[JobLevel] [nvarchar](10) NULL,
[Date] [datetime] NULL,
[IsCurrent] [tinyint] NULL,
[Status] [tinyint] NULL,
[Skill] [nvarchar](50) NULL,
[BroadSkill] [nvarchar](500) NULL,
[Location] [nvarchar](50) NULL,
[CurrentSBU] [nvarchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CurrentJobHistory] ADD CONSTRAINT [DF_CurrentJobHistory_Status] DEFAULT ((0)) FOR [Status]
GO
sample data
------------
insert into dbo.CurrentJobHistory
select 'A-6141',4,'D2','S75','S75','L30025','2014/03/04','ITS',1,0
insert into dbo.CurrentJobHistory
select 'A-6142',10,'D2','S75','S75','L30025','2014/03/04','ITS',1,0
insert into dbo.CurrentJobHistory
select 'A-6143',4,'D3','S75','S75','L30025','2014/03/04','ITS',1,0
With the sample data,the output should be something like the image attached.
What I require is remove the first column(here)(which has zero values for all rows.
Hope you have understood the scenario.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 9, 2015 at 7:36 am
You might want to test your script before you post them.
ProjectAssignment table inserts.
Msg 213, Level 16, State 1, Line 5
Column name or number of supplied values does not match table definition.
Here is the inserts to CurrentJobHistory
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2015 at 10:42 pm
Sorry Sean..
I should have tested the order of data in the insert statement.Please find the orrect statements below:
inserting to' ProjectAssignment'
-----------------------------------
insert into dbo.ProjectAssignment
select 'A-6140','2014/03/04','P1','L30025',0,0,1,NULL,'','','',3,''
insert into dbo.ProjectAssignment
select 'A-6141','2014/03/04','P1','L30025',0,0,1,NULL,'','','',3,''
insert into dbo.ProjectAssignment
select 'A-6142','2014/03/04','P2','L30025',0,0,1,NULL,'','','',3,''
Inserting to 'CurrentJobHistory'
-----------------------------------
insert into dbo.CurrentJobHistory
select 'A-6141','D2',4,'2014/03/04',1,0,'S75','S75','L30025','ITS'
insert into dbo.CurrentJobHistory
select 'A-6142','D2',10,'2014/03/04',1,0,'S75','S75','L30025','ITS'
insert into dbo.CurrentJobHistory
select 'A-6143','D3',4,'2014/03/04',1,0,'S75','S75','L30025','ITS'
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 10, 2015 at 7:40 am
The only way you will be able to conditionally select columns is with dynamic sql. That means that you are going to have to first run your dynamic pivot and store the results so you can evaluate those results and dynamically get columns from it. Here is one way to do it. I would not recommend this as the performance is likely to be pretty awful if these datasets are even approaching a decent size.
Please note I had to remove the join predicate "AND P.SBUCode=''ITS''" because in the sample data you provided there were no values in that column at all. I have no idea if this is really what you want because the picture you posted doesn't seem to match the sample data provided.
create table #Results
(
Project varchar(20)
, Total varchar(20)
, GroupTestMgr varchar(20)
, SDM varchar(20)
, TestConsultant varchar(20)
)
insert #Results
EXEC(@RunQry)
select * from #Results
set @RunQry = 'select Project'
select @RunQry = @RunQry + case when (select MAX(Total) from #Results) > 0 then ', Total' else '' end
select @RunQry = @RunQry + case when (select MAX(GroupTestMgr) from #Results) > 0 then ', GroupTestMgr' else '' end
select @RunQry = @RunQry + case when (select MAX(SDM) from #Results) > 0 then ', SDM' else '' end
select @RunQry = @RunQry + case when (select MAX(TestConsultant) from #Results) > 0 then ', TestConsultant' else '' end
set @RunQry = @RunQry + ' from #Results'
exec sp_executesql @RunQry
drop table #Results
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2015 at 2:25 am
Sean,
Thanks for the answer.
This will work only when the designations are known beforehand,right?
In my case the designations are not fixed and we take the values from the designation table.
Any solution for this issue?
regards,
Sunitha
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 13, 2015 at 7:22 am
sunitkrishna (4/13/2015)
Sean,Thanks for the answer.
This will work only when the designations are known beforehand,right?
In my case the designations are not fixed and we take the values from the designation table.
Any solution for this issue?
regards,
Sunitha
That is correct. You will have to add another level of dynamic sql here to build the columns dynamically. I showed you how to do the hard part. I will leave the dynamic columns up to you. What have you tried there?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2015 at 2:41 am
Sean,
Sorry for the late reply.
Since the requirement needed urgent solution we fixed it from the code behind.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply