change table dimension

Question

0.00/5 (No votes)

See more:

SQL-server-2000

dear sir/ma'am

can anybody help me in framing this query :Following are the input & output
Input table:

Empno PhoneType Number
100   Mobile    1234
100   Telephone 3456
101   Mobile    5678
102   Telephone 7890
103   Mobile    3412

Output:

Empno Mobile Telephone
100   1234   3456
101   5678   -
102   -      7890
103   3412   -

Posted 3-Jul-13 1:00am

ankur789

Updated 3-Jul-13 1:10am

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

Try this:

SQL

DECLARE @PhoneDtls TABLE (Empno INT, PhoneType VARCHAR(20), Number VARCHAR(20))

INSERT INTO @PhoneDtls(EmpNo,PhoneType,Number)
SELECT 100,'Mobile','1234'
UNION ALL SELECT 100,'Telephone','3456'
UNION ALL SELECT 101,'Mobile','5678'
UNION ALL SELECT 102,'Telephone','7890'
UNION ALL SELECT 103,'Mobile','3412'
SELECT EmpNo,PhoneType,Number FROM @PhoneDtls

SELECT Empno, ISNULL([Mobile],'-') 'Mobile', ISNULL([Telephone] ,'-') 'Telephone'
FROM (
    SELECT *
    FROM @PhoneDtls
) AS DT
PIVOT(MAX([Number]) FOR [PhoneType] IN([Mobile], [Telephone])) AS PT

More: Using PIVOT and UNPIVOT[^]

Posted 3-Jul-13 1:14am

Maciej Los

Updated 4-Jul-13 0:56am

v5

Comments

ankur789 3-Jul-13 7:16am

incorrect syntax near pivot

Maciej Los 3-Jul-13 7:26am

Check now ;)

ankur789 3-Jul-13 7:28am

Sir incorrect syntax near pivot

Maciej Los 3-Jul-13 7:32am

Now, should be OK ;)

gvprabu 4-Jul-13 6:57am

hi check the solution... there is no error in this

gvprabu 4-Jul-13 6:56am

your solution is abs correct.... :-) my 5+

Maciej Los 4-Jul-13 8:08am

Thank you, Gopal.
I see that you've change my solution, adding ISNULL part ;) Thanks!

gvprabu 4-Jul-13 8:12am

ha ha ha :-)....

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)

gvprabu · Accepted Answer · 2013-07-03T22:36:00

Solution 2

Hi,

Try the below Code...

SQL

DECLARE @PhoneDtls TABLE (Empno INT, PhoneType VARCHAR(20), Number VARCHAR(20))

INSERT INTO @PhoneDtls(EmpNo,PhoneType,Number)
SELECT 100,'Mobile','1234'
UNION ALL SELECT 100,'Telephone','3456'
UNION ALL SELECT 101,'Mobile','5678'
UNION ALL SELECT 102,'Telephone','7890'
UNION ALL SELECT 103,'Mobile','3412'
SELECT EmpNo,PhoneType,Number FROM @PhoneDtls


SELECT T.EmpNo, 
	ISNULL((SELECT Number FROM @PhoneDtls WHERE PhoneType ='Mobile' AND EmpNo=T.EmpNo),'-') 'Mobile',
	ISNULL((SELECT Number FROM @PhoneDtls WHERE PhoneType ='Telephone' AND EmpNo=T.EmpNo),'-') 'Number'
FROM (SELECT DISTINCT EmpNo FROM @PhoneDtls) T

Regards,
GVPrabu

Posted 3-Jul-13 22:36pm

gvprabu

Updated 4-Jul-13 0:52am

v3

Comments

ArunRajendra 4-Jul-13 4:43am

This will not give the recored in same row. The resut will be shown in two rows.

gvprabu 4-Jul-13 6:53am

I updated my solution, check it now.

gvprabu 4-Jul-13 4:48am

yes correct... I will change the code. Thanks

ankur789 4-Jul-13 5:13am

This will not give the recored in same row. The resut will be shown in two rows.
I wants result in one row

Maciej Los 4-Jul-13 6:39am

See my solution.
Is there something wrong with it?

gvprabu 4-Jul-13 6:53am

I updated my solution, check it now.

gvprabu 4-Jul-13 6:48am

yes yar... I will do this and post soon :-)

Maciej Los 4-Jul-13 8:09am

Above message was intended to OP ;)
BTW, +5!