July 4, 2011 at 4:30 am
Hi,
I am working on a Human Resource Application using MS SQL Server 2008 Express Edition.
I have following Tables:
1. Employees
2. BudgetCode
Employee Table:
EmployeeId(Primary Key, Participates in relation with other tables), EmployeeCode (UNIQUE Key), Name, SSN, Gender)
BudgetCode Table:
BudgetCodeId, EmpId ( FK to EmployeeId), BudgetCode, Percentage)
Relation: This relation is one-to-many relation between Employee Table. So one employee can have many BudgetCodes in BudgetCode Table.
My desired result is like,
EmployeeCode, Name, BudgetCode1,BudgetCode2,BudgetCode3...BudgetCodeN
Example:
Employee Table:
EmployeeId, EmployeeCode, Name, SSN, Gender
1, CL7653, Paul, WT, M
BudgetCode Table:
BudgetCodeId, EmpId, BudgetCode, Percentage
1, 1, US8765, 50
1, 2, US8543, 50
Desired Result:
EmployeeCode, Name, BudgetCode1, BudgetCode2
CL7653, Paul, US8765, US8543
Can any give solution to the problem, i have tried using PIVOT and some other hacks but failed to resolve. I appreciate your response
Thanks
July 4, 2011 at 4:42 am
July 4, 2011 at 6:28 am
revozex (7/4/2011)
EmployeeCode, Name, BudgetCode1,BudgetCode2,BudgetCode3...BudgetCodeN
The N part makes it harder.
revozex (7/4/2011)
Example:Employee Table:
EmployeeId, EmployeeCode, Name, SSN, Gender
1, CL7653, Paul, WT, M
BudgetCode Table:
BudgetCodeId, EmpId, BudgetCode, Percentage
1, 1, US8765, 50
1, 2, US8543, 50
Desired Result:
EmployeeCode, Name, BudgetCode1, BudgetCode2
CL7653, Paul, US8765, US8543
Think you messed up your example. EmpId 1 only have one BudgetCode connected to him. And as such cant get the result you have. Me thinks you swiched places on the BudgetCodeId and EmpId columns (or their values).
Anyway here is my little go at the problem.
-- Our tables (i have dropped the columns that wasnt needed)
create table Employee (EmployeeId int Primary Key, EmployeeCode varchar(32), Name varchar(32))
create table BudgetCode (BudgetCodeId int, EmpId int, BudgetCode varchar(32) primary key (BudgetCodeId, EmpId))
go
--A little more testdata
insert into Employee values (1, 'CL7653', 'Paul')
insert into Employee values (2, 'CL7652', 'Donald')
insert into BudgetCode values (1, 1, 'US8765')
insert into BudgetCode values (2, 1, 'US8543')
insert into BudgetCode values (1, 2, 'US8513')
insert into BudgetCode values (3, 2, 'US8512')
insert into BudgetCode values (4, 2, 'US8545')
insert into BudgetCode values (10, 2, 'US1010')
go
--Since the nr of columns are unknown we have to find out the max nr of budgets that a single employee have
declare @MaxBudgetNr int
--could have used as simple count(*) / group by here instead
select @MaxBudgetNr = Max(Nr) from (select Row_Number() over (partition by EmpId order by EmpId, BudgetCodeId) Nr from BudgetCode) x
declare @sql varchar(max)
declare @i integer
--Create our dynamic sql
select @sql = 'select e.EmployeeCode, e.Name, ', @i = 1
--a small loop to create all the columns
while @i <= @MaxBudgetNr
begin
select @sql = @sql + ' Max(case when Nr = ' + Convert(varchar(3), @i) + ' then b.BudgetCode else '''' end) BudgetCode' + convert(varchar(3), @i) + case when @i < @MaxBudgetNr then ', ' else ' ' end
set @i = @i + 1
end
select @sql = @sql + ' from (select BudgetCodeId, EmpId, BudgetCode, Row_Number() over (partition by EmpId order by EmpId, BudgetCodeId) Nr from BudgetCode) b join Employee e on e.EmployeeId = b.EmpId group by e.EmployeeCode, e.Name'
--And execute
exec(@sql)
go
drop table Employee
drop table BudgetCode
Should be noted that its more or less based on Mr Modens article above.
/T
July 4, 2011 at 6:42 am
@tomm:
you are right It's a mistake Example Table would be
BudgetCodeId, EmpId, BudgetCode, Percentage
1, 1, US8765, 50
2, 1, US8543, 50
Now corrected table explains an employee having more than Budget Codes.
July 4, 2011 at 8:53 am
Tom, I'll borrow your test data...
Another possible solution:
IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
DROP TABLE #Employee
IF OBJECT_ID('tempdb..#BudgetCode') IS NOT NULL
DROP TABLE #BudgetCode
CREATE TABLE #Employee (
EmployeeId int NOT NULL PRIMARY KEY,
EmployeeCode varchar(6) NOT NULL UNIQUE,
Name varchar(50) NOT NULL,
SSN varchar(50) NULL,
Gender char(1) NULL
)
CREATE TABLE #BudgetCode (
BudgetCodeId int NOT NULL,
EmpId int NOT NULL,
BudgetCode varchar(6) NOT NULL,
Percentage int NULL
)
insert into #Employee (EmployeeId, EmployeeCode, Name) values (1, 'CL7653', 'Paul')
insert into #Employee (EmployeeId, EmployeeCode, Name) values (2, 'CL7652', 'Donald')
insert into #BudgetCode (BudgetCodeId, EmpId, BudgetCode) values (1, 1, 'US8765')
insert into #BudgetCode (BudgetCodeId, EmpId, BudgetCode) values (2, 1, 'US8543')
insert into #BudgetCode (BudgetCodeId, EmpId, BudgetCode) values (1, 2, 'US8513')
insert into #BudgetCode (BudgetCodeId, EmpId, BudgetCode) values (3, 2, 'US8512')
insert into #BudgetCode (BudgetCodeId, EmpId, BudgetCode) values (4, 2, 'US8545')
insert into #BudgetCode (BudgetCodeId, EmpId, BudgetCode) values (10, 2, 'US1010')
DECLARE @sql nvarchar(max)
SELECT @sql = STUFF((
SELECT DISTINCT ',' + QUOTENAME(ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY BudgetCodeId)) AS [text()]
FROM #BudgetCode
ORDER BY 1
FOR XML PATH('')
),1, 1, SPACE(0))
SET @sql =
'WITH Budget AS ( ' +
' SELECT EmpId, BudgetCode, RN = ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY BudgetCodeId) ' +
' FROM #BudgetCode ' +
') ' +
'SELECT EmpId, ' + @sql + ' ' +
'FROM Budget AS src ' +
'PIVOT ( MIN(BudgetCode) FOR RN IN ('+ @sql +')) AS pvt '
EXEC (@sql)
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply