March 21, 2017 at 3:39 pm
Hi All,
I have a following query:
select FName + ' ' + Lname as [Name], employeeId, email, [name] as [Project Name], hrs1, hrs2, hrs3, hrs4, hrs5, hrs6, hrs7, hrs8, hrs9, hrs10, hrs11, hrs12, hrs13, hrs14
from
tbl_lookup INNER JOIN tbluser
ON tbl_lookup .UserID = tbluser.ID
INNER JOIN tbl_project
ON tbl_time.ProjectID = tbl_Project.ID
The outcome of the query is below:
Name employeeID email Project Name hrs1 hrs2 hrs3 hrs4 hrs5 hrs6 hrs7 hrs8 hrs9 hrs10 hrs11 hrs12 hrs13 hrs14
TestName1 1234 Test@Test1.com Project A 6 7 8 9 6 2 2 1 0 1 1 3 2 2
Testname1 1234 Test@Test1.com Project B 1 2 1 4 1 2 5 5 0 1 0 1 1 2
Testname1 1234 Test@Test1.com Project B
Testname1 1234 Test@Test1.com Project B
Testname2 4567 Test@Test2.com Project B 1 2 1 4 1 2 1 1 0 1 0 1 1 2
Testname2 14567 Test@Test2.com Project A 1 2 1 4 1 2 2 2 0 1 0 1 1 2
Is it possible to get the same result differently like
Name Project A Project B Project C
Testname1 50 26
Testname2 20 18
I want to list all the Project names at the top as a row header and Names as the first column and then total hours for each project.
any help will be appreciated.
March 22, 2017 at 3:45 am
This seems like the good case for the PIVOT expression. How far away is the below example? I've assumed "tbl_lookup" = "tbl_time" in your above example.
IF OBJECT_ID('tempdb.dbo.#tbl_Time') IS NOT NULL
DROP TABLE #tbl_Time
CREATE TABLE #tbl_Time (
EmployeeID int
, ProjectID int
, hrs1 smallint
, hrs2 smallint
, hrs3 smallint
, hrs4 smallint
, hrs5 smallint
, hrs6 smallint
, hrs7 smallint
, hrs8 smallint
, hrs9 smallint
, hrs10 smallint
, hrs11 smallint
, hrs12 smallint
, hrs13 smallint
, hrs14 smallint
)
insert into #tbl_Time(
employeeID
, projectid
, hrs1
, hrs2
, hrs3
, hrs4
, hrs5
, hrs6
, hrs7
, hrs8
, hrs9
, hrs10
, hrs11
, hrs12
, hrs13
, hrs14
)
values
(1, 1 ,6,7,8,9,6,2,2,1,0,1,1,3,2,2)
, (1, 2, 1,2,1,4,1,2,5,5,0,1,0,1,1,2)
, (1, 2, null,null,null,null,null,null,null,null,null,null,null,null,null,null)
, (1, 2, null,null,null,null,null,null,null,null,null,null,null,null,null,null)
, (2, 2, 1,2,1,4,1,2,1,1,0,1,0,1,1,2)
, (2, 1, 1,2,1,4,1,2,2,2,0,1,0,1,1,2)
IF OBJECT_ID('tempdb.dbo.#tbl_Project') IS NOT NULL
DROP TABLE #tbl_Project
CREATE TABLE #tbl_Project (
ProjectID int
, [Name] nvarchar(200)
)
insert into #tbl_Project(
ProjectID
, [Name]
)
values
(1, 'Project A')
, (2, 'Project B')
IF OBJECT_ID('tempdb.dbo.#tbl_User') IS NOT NULL
DROP TABLE #tbl_User
CREATE TABLE #tbl_User (
EmployeeID int
, [Name] nvarchar(200)
)
insert into #tbl_User(
EmployeeID
, [Name]
)
values
(1, 'TestName1')
, (2, 'TestName2')
declare @projectlist nvarchar(max) = ''
-- Get our list of distinct project names to use as column headers in the below PIVOT. Remove any square-brackets from the project names so these don't cause the below query to error or create an injection vulnerability.
select @projectlist = @projectlist + ',' + '[' + replace(replace([Name], '[',''), ']','') + ']'
from (
select distinct [Name]
from #tbl_Project as projectwork
) as projectwork
-- Remove the "," from the start of @projectlist
set @projectlist = right(@projectlist, len(@projectlist)-1)
declare @dynamicquery nvarchar(max) = '
select Name, ' + @projectlist + '
from (
select #tbl_User.[Name], #tbl_Project.[Name] as projectname
, sum(isnull(hrs1,0))
+ sum(isnull(hrs2,0))
+ sum(isnull(hrs3,0))
+ sum(isnull(hrs4,0))
+ sum(isnull(hrs5,0))
+ sum(isnull(hrs6,0))
+ sum(isnull(hrs7,0))
+ sum(isnull(hrs8,0))
+ sum(isnull(hrs9,0))
+ sum(isnull(hrs10,0))
+ sum(isnull(hrs11,0))
+ sum(isnull(hrs12,0))
+ sum(isnull(hrs13,0))
+ sum(isnull(hrs14,0)) as hoursworked
from #tbl_Time
inner join #tbl_Project
on #tbl_Time.ProjectID = #tbl_Project.ProjectID
inner join #tbl_User
on #tbl_Time.EmployeeID = #tbl_User.EmployeeID
group by #tbl_Project.[Name]
, #tbl_User.[Name]
) as sourcetable
pivot (
sum(hoursworked)
for projectname in(' + @projectlist + ')
) as pivottable'
exec (@dynamicquery)
March 24, 2017 at 11:40 am
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply