Converting Rows to Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Converting Rows to Columns

Author

Topic

LearningIT
Starting Member

5 Posts

Posted - 2011-07-04 : 06:23:27

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-04 : 06:36:06

Hm...I suspect you need dynamic pivoting. Madhi has an article about it written some time ago but it still aplies:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-04 : 07:29:02

quote:
Originally posted by Lumbago

Hm...I suspect you need dynamic pivoting. Madhi has an article about it written some time ago but it still aplies:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

You can *use* dynamic pivoting..... But that doesn't mean that you should. this is a presentation issue, not a database task.

The more sensible thing to do would be to return the results as a simple list (with the employee codes, then budget codes in order)

Then iterate over the list in whatever application is calling the database and every time the employeeCode changes drop down to the next row.

if the result set is a sane size then storing this would probably involve a dictionary of EmployeeCode to List of BudgetCodes.

Then at the end write the dictionary to wherever it needs to go.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources