sql retrieve problem

Question

0.00/5 (No votes)

See more:

Table Employee

emp_id | emp_name

01 | john

02 | mitch

Table Salary

s_id | income | emp_id
1 | 10,000 | 02
2 | 20,000 | 01
3 | 5,000 | 02
4 | 2,000 | 01

I want sql command to output the data like this:

john | 20,000 | 2,000
mitch | 10,000 | 5,000

pls help me. I use VB.NET programming language

Posted 17-Jun-12 20:18pm

Member 8439254

Updated 17-Jun-12 20:19pm

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2012-06-17T20:34:00

Solution 1

Looks like PIVOT may provide an answer[^] to your problem.

Posted 17-Jun-12 20:34pm

Abhinav S

Sandeep Mewara · Answer 2 · 2012-06-17T20:36:00

Solution 2

I want sql command to output the data like this:
Did you try anything by yourself?

Having data for employee row-wise would be simple, like:

SQL

SELECT
  em.emp_name,
  sa.income
FROM
  Employee em
INNER JOIN
  Salary sa
ON em.emp_id = sa.emp_id

But, what you ask after this is to collate all the same employee id row data to column data. It would not be simple through query. I would suggest you to take the above query data and then mould it into the format required in your frontend.

Posted 17-Jun-12 20:36pm

Sandeep Mewara

Comments

Member 8439254 18-Jun-12 4:24am

My problem is for frontend displaying of data.

Sandeep Mewara 18-Jun-12 5:16am

Once you have this data from the query I specified, loop through the datatable. Create a new datatable and add/copy details in the format you want. You can loop and see/find all records to copy for an employee. So, copy employee wise data.

Prosan · Answer 3 · 2012-06-17T20:36:00

Solution 3

use pivot table

SQL

with Employee_Sal as
(select A.emp_name,
B.income,
from Employee A join Salary B on A.emp_id = B.emp_id)

Posted 17-Jun-12 20:36pm

Prosan

Comments

Member 8439254 18-Jun-12 3:58am

I can't find a basic example regards on pivot sql. I read many codes but don't im in trouble to make my own code.