multi column subquery? w/ alias

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 column subquery? w/ alias

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-12 : 15:01:56

What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:

when pricelist = 1

when pricelist = 2

when pricelist = 3

pricelist
--------
1
2
3

Price
--------
912 -- (linked with 1)
234 -- (linked with 3)
56 -- (linked with 2)
3245 -- (linked with 3)
234 -- (linked with 1)
65 -- (linked with 2)

these 2 columns are in the same table^^

so what i want my query to generate is:

Price1
--------
912
234

Price2
--------
56
65

Price3
--------
234
3245

Any help is apprecieated, thanks

if the above does not make sense to you maybe this will:
"can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 15:36:59

On what basis 1,2 and 3 are linked to the table price? .. can you post in more details

Chirag

http://www.chirikworld.com

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-12 : 15:50:41

quote:
Originally posted by chiragkhabaria

On what basis 1,2 and 3 are linked to the table price? .. can you post in more details

Chirag

http://www.chirikworld.com

i only wrote linked to assist with the logic..

i want to show the values of price when pricelist = 1..
same when it = 2
same when it = 3

and assign each 1 of these a unique alias

Price and PriceList are columns inside the same table

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-12 : 15:56:48

I have no idea what you are asking us ...

- Jeff
http://weblogs.sqlteam.com/JeffS

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 16:01:18

[code]
Something like this

Select ( Case When PriceList = 1 Then Price End ) As Price 1,
( Case When PriceList = 2 Then Price End ) As Price 2,
( Case When PriceList = 1 Then Price End ) As Price 3
From yourtable
[/code]

Chirag

http://www.chirikworld.com

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-12 : 16:02:56

quote:
Originally posted by jsmith8858

I have no idea what you are asking us ...

- Jeff
http://weblogs.sqlteam.com/JeffS

can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 16:15:54

sorry not able to get you .. can you post your table structure and excepted output in a clear format..!!!

Chirag

http://www.chirikworld.com

DMarmolejos
Yak Posting Veteran

65 Posts

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

quote:
Originally posted by chiragkhabaria

sorry not able to get you .. can you post your table structure and excepted output in a clear format..!!!

Chirag

http://www.chirikworld.com

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

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

Price
-----
777
888
999
777
888
999
777
888
999

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 expect is:

PRICE1
------
777
777
777

PRICE2
------
888
888
888

PRICE3
-----
999
999
999

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 16:32:34

Did you tried the query which i had posted earlier?

Chirag

http://www.chirikworld.com

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-12 : 16:38:12

quote:
Originally posted by chiragkhabaria

Did you tried the query which i had posted earlier?

Chirag

http://www.chirikworld.com

that query didnt work for me..

check my last post.. i just edited it.. see if that makes sense to you and if u can make a query to display my output

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 17:06:33

Something like this ?


Select * From 
(
	Select 'Price 1' As Price union all
	Select cast(Price as varchar(10)) From yourtable Where Pricelist = 1
	union all 
	Select 'Rrice 2' Union All
	Select cast(Price as varchar(10)) From yourtable able Where Pricelist = 2
	union all 
	Select 'Price 3' Union all 
	Select cast(Price as varchar(10)) From yourtable Where Pricelist = 3
) As f

Chirag

http://www.chirikworld.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources