Completed

SQL Procedure \ Function Help

This project was successfully completed by emmett25ro for $30 USD in a day.

Get free quotes for a project like this
Employer working
Completed by:
Skills Required
Project Budget
$30 - $250 USD
Completed In
1 day
Total Bids
40
Project Description

I need some helps on sql server 2008. I have a hard time to figure out how to get back the data as I want.

The multiple ids in people field are stored in the same rows and seperated by pile(|),

Jobs column are doing the same.

I know It doesn't look right on database design, but the datatable is provided by 3rd parties,

so I have no choose to modify it.

I believe I need a store procedure\function or some sql server special function to do that

this is my tables

*Helpdesk Table

id People Jobs

-------------------------------------------------------------

100 1|2 7|8|9

*People Table

id FullName

-----------------------------------------------------

1 Nick

2 John

*Jobs Table

id JobName

-----------------------------------------------------

7 Software

8 Hardware

9 Network

So, I want to select the data like this way and return the data like this:

select id, ConvertFunc(People,'id','FullName') as NewPeople, ConvertFunc(Jobs,'id','JobName') as NewJobs from helpdesk

and return:

id NewPeople NewJobs

------------------------------------------------------

100 Nick, John Software, Hardware, Network

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online