I am working on a Human Resource Application using MS SQL Server 2008 Express Edition.
I have following Tables:
EmployeeId(Primary Key, Participates in relation with other tables), EmployeeCode (UNIQUE Key), Name, SSN, Gender)
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
EmployeeId, EmployeeCode, Name, SSN, Gender
1, CL7653, Paul, WT, M
BudgetCodeId, EmpId, BudgetCode, Percentage
1, 1, US8765, 50
1, 2, US8543, 50
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