union with itself??? - 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)

union with itself???

Author

Topic

greatbear302
Starting Member

13 Posts

Posted - 2009-07-01 : 13:12:42

(can't think of approptiate title)
i have a table with emp_id, phone_type and phone_number in it, something like, table Phones:
emp_id phone_type phone_number
-------------------------------
001 home 12345
001 cell 12346
001 fax 12347
001 cell 12348
001 home 12349
002 cell 12340

and i want to use ONLY one query to get a result set like:
emp_id home_number cell_number fax_number
------------------------------------------
001 12345 12346 12347
001 12349 12348
002 12340

how do i acheive this? i tried to use UNION of Phones with Phones, with WHERE, but did not work. also, i do not want to use pivot or unpivot.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-07-01 : 13:20:00

well my first answer was going to be pivot...why don't you want to do that?

Mike
"oh, that monkey is going to pay"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:24:02

[code]SELECT emp_id,
MAX(CASE WHEN phone_type='home' THEN phone_number ELSE NULL END) AS home_number,
MAX(CASE WHEN phone_type='cell' THEN phone_number ELSE NULL END) AS cell_number,
MAX(CASE WHEN phone_type='fax' THEN phone_number ELSE NULL END) AS fax_number
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id,phone_type ORDER BY phone_type) AS Seq,*
FROM Phones)t
GROUP BY emp_id,Seq
[/code]

greatbear302
Starting Member

13 Posts

Posted - 2009-07-01 : 13:36:19

Prof, reason i dont want to use pivot is because i'm not using sql server as my db. i know...this is sql server forum, but this forum has just about the quickest replies ive ever seen...
im using oracle...and for some reason i beleive this quesry can be solved just by ANSI/ISO SQL. maybe im wrong....

greatbear302
Starting Member

13 Posts

Posted - 2009-07-01 : 13:39:05

quote:

Originally posted by visakh16

SELECT emp_id,
MAX(CASE WHEN phone_type='home' THEN phone_number ELSE NULL END) AS home_number,
MAX(CASE WHEN phone_type='cell' THEN phone_number ELSE NULL END) AS cell_number,
MAX(CASE WHEN phone_type='fax' THEN phone_number ELSE NULL END) AS fax_number
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id,phone_type ORDER BY phone_type) AS Seq,*
FROM Phones)t
GROUP BY emp_id,Seq

visakh...im working on your solution...im getting a missing expression error...its an oracle error but still trying to figure out why the error should come....because u are using all standard clauses

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 13:45:40

oracle error? are you using oracle? then my solution may not work as its sql server specific. i'm not sure whether you've row_number in oracle

greatbear302
Starting Member

13 Posts

Posted - 2009-07-01 : 14:18:10

quote:
Originally posted by visakh16

oracle error? are you using oracle? then my solution may not work as its sql server specific. i'm not sure whether you've row_number in oracle

yes there is row_number() in oracle

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 14:22:52

when you have two home numbers and two cell numbers for the same emp_id, does it matter what combination comes in the first line (along with the fax number) or what comes in the second line?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 14:27:52

quote:
Originally posted by greatbear302

quote:
Originally posted by visakh16

oracle error? are you using oracle? then my solution may not work as its sql server specific. i'm not sure whether you've row_number in oracle

yes there is row_number() in oracle

then what was error you got?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 02:14:43

quote:
Originally posted by greatbear302

quote:
Originally posted by visakh16

SELECT emp_id,
MAX(CASE WHEN phone_type='home' THEN phone_number ELSE NULL END) AS home_number,
MAX(CASE WHEN phone_type='cell' THEN phone_number ELSE NULL END) AS cell_number,
MAX(CASE WHEN phone_type='fax' THEN phone_number ELSE NULL END) AS fax_number
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id,phone_type ORDER BY phone_type) AS Seq,*
FROM Phones)t
GROUP BY emp_id,Seq

visakh...im working on your solution...im getting a missing expression error...its an oracle error but still trying to figure out why the error should come....because u are using all standard clauses


try this i think u got error at as alias name
SELECT emp_id,
MAX(CASE WHEN phone_type='home' THEN phone_number ELSE NULL END)"home_number",
MAX(CASE WHEN phone_type='cell' THEN phone_number ELSE NULL END)"cell_number",
MAX(CASE WHEN phone_type='fax' THEN phone_number ELSE NULL END) "fax_number"
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id,phone_type ORDER BY phone_type) AS Seq,*
FROM Phones)t
GROUP BY emp_id,Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 13:06:10

quote:

Originally posted by bklr

quote:
Originally posted by greatbear302

quote:
Originally posted by visakh16

SELECT emp_id,
MAX(CASE WHEN phone_type='home' THEN phone_number ELSE NULL END) AS home_number,
MAX(CASE WHEN phone_type='cell' THEN phone_number ELSE NULL END) AS cell_number,
MAX(CASE WHEN phone_type='fax' THEN phone_number ELSE NULL END) AS fax_number
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id,phone_type ORDER BY phone_type) AS Seq,*
FROM Phones)t
GROUP BY emp_id,Seq

visakh...im working on your solution...im getting a missing expression error...its an oracle error but still trying to figure out why the error should come....because u are using all standard clauses


try this i think u got error at as alias name
SELECT emp_id,
MAX(CASE WHEN phone_type='home' THEN phone_number ELSE NULL END)"home_number",
MAX(CASE WHEN phone_type='cell' THEN phone_number ELSE NULL END)"cell_number",
MAX(CASE WHEN phone_type='fax' THEN phone_number ELSE NULL END) "fax_number"
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id,phone_type ORDER BY phone_type) AS Seq,*
FROM Phones)t
GROUP BY emp_id,Seq

do you mean oracle wont accept AS?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources