multi aliases with the same column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

multi aliases with the same column?

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-17 : 12:37:39

TABLE1
======================

PriceList
---------
1
2
3
1
2
3
1
2
3

Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================

I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3

the output that i want is:

PRICE1
------
777
777
777

PRICE2
------
888
888
888

PRICE3
-----
999
999
999
(pretend these columns are also side by side)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 12:41:17

Cant you do a simple select?

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-17 : 12:45:28

quote:
Originally posted by visakh16

Cant you do a simple select?

no because each alias needs a different WHERE condition

SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3
FROM TABLE1

^ that would give me an error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 13:01:10

Where do you want to show data?

SELECT PRICE FROM TABLE1 WHERE PRICELIST in (1,2,3) ORDER BY PRICELIST

Now when you show data in front end, show them in the specified order

Madhivanan

Failing to plan is Planning to fail

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-17 : 13:09:50

quote:
Originally posted by madhivanan

Where do you want to show data?

SELECT PRICE FROM TABLE1 WHERE PRICELIST in (1,2,3) ORDER BY PRICELIST

Now when you show data in front end, show them in the specified order

Madhivanan

Failing to plan is Planning to fail

that code is good but it orders it in 1 column

i need my output to have 3 seperate columns with 3 seperate aliases

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 13:14:14

quote:
Originally posted by DMarmolejos

quote:
Originally posted by madhivanan

Where do you want to show data?

SELECT PRICE FROM TABLE1 WHERE PRICELIST in (1,2,3) ORDER BY PRICELIST

Now when you show data in front end, show them in the specified order

Madhivanan

Failing to plan is Planning to fail

that code is good but it orders it in 1 column

i need my output to have 3 seperate columns with 3 seperate aliases

If you have unique column, you can do


Select 	unique_col,
	max(case when pricelist=1 then price end) as price1,
	max(case when pricelist=2 then price end) as price2,
	max(case when pricelist=3 then price end) as price3
from
	table1
group by
	unique_col

Madhivanan

Failing to plan is Planning to fail

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-17 : 13:48:50

THANK YOU! you are the man! haha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources