Converting Rows to Columns

Databases
1

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 :slight_smile:

Thanks

2

you should really be doing the re-formatting of results in the application layer (asp or .net or whatever you’re using) – it’s much more efficient that way

if you insist on doing it in SQL, please show your PIVOT or other hacks attempts