how to get the column output as column name in sql server with values

Question

3.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,

I have 2 tables.

TABLE: EMP_DETail

EMP_ID     EMP_NAme     EMP_LAST_NAME
 1         inayat       basha
 2         santosh      muddala
 3         pavan        sree

TABLE: EMP_ADDRESS

EMP_ID     TYPE     PHONE_NO
  1        RES      080-2414
  1        CELL     987451
  1        OFF      080241
  2        RES      028741
  2        CELL     87414
  2        OFF      21474
  3        RES      04087845
  3        CELL      874555

These are the two tables i have, and i want the out put as

EMP_ID    EMP_NAME   EMP_LAST NAME      CELL       RES
 1        inayat     basha              981451     080-2414
 2        santosh    maddula            87414      028741
 3        pavan      sree               874555     04087845

Regards,
S.Inayat Basha.

[edit]Code blocks added to tidy up display, "Ignore HTML..." option disabled - OriginalGriff[/edit]

Posted 24-Mar-11 21:15pm

inayat basha

Updated 24-Mar-11 21:48pm

OriginalGriff

v2

Add a Solution

Comments

RDBurmon 25-Mar-11 4:21am

Is this type are fixed ? like is there any type out of RES,CELL,OFF,

2 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)

Wendelius · Answer 1 · 2011-03-25T08:19:00

You can also use normal join and join the EMP_ADDRESS table twice. Something like

SELECT ed.*,
       ea1.Phone_No AS Cell,
       ea2.Phone_No AS Res
FROM   Emp_Detail ed 
       INNER JOIN Emp_Address ea1 ON ed.Emp_Id = ea1.Emp_Id
       INNER JOIN Emp_Address ea2 ON ed.Emp_Id = ea2.Emp_Id 
WHERE  ea1.Type = 'RES'
AND    ea2.Type = 'CELL'

If cell and res are not mandatory rows use LEFT JOIN instead

prasenjeit · Answer 2 · 2011-03-25T00:55:00

you can try something like this:

SQL

Select (det.emp_id,det.emp_name,det.emp_last_name,
(select phone_no from table emp_address where epmid =det.emp_id and type = 'cell') as 'cell',(select phone_no from table emp_address where epmid =det.emp_id and type = 'Res') as 'Res')
from table emp_detail det

I am currently not having SQL to check the query but the logic could be like this.

[EDIT] Used "pre" tag for the code

how to get the column output as column name in sql server with values

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0