Using sub-query as column name? - 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)

Using sub-query as column name?

Author

Topic

tentonipete
Starting Member

4 Posts

Posted - 2007-07-31 : 13:26:12

I have 2 tables as follows.

Basket:
pID | quantity | Member Type
3 | 4 | TypeA
1 | 2 | TypeC
2 | 7 | TypeA

Product (price info):
pID | TypeA | TypeB | TypeC
1 | 10 | 20 | 15
2 | 8 | 11 | 10
3 | 20 | 30 | 24

I wish to return a list of prices from the "Basket" looking up prices in the "Product" table.

Result:
pID | Price
3 | 20
1 | 15
2 | 8

I thought I could use a sub-query as the name of the column I wish to return the value from but having attempted this it does not seem to work.

Could anyone suggest a way I could proceed? I would love to change the table structure but unfortunately my hands are tied.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-31 : 14:39:31

Are those three the only types or could there be more?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 14:51:35

[code]-- Prepare sample data
DECLARE @Basket TABLE (pID INT, quantity INT, MemberType SYSNAME)

INSERT @Basket
SELECT 3, 4, 'TypeA' UNION ALL
SELECT 1, 2, 'TypeC' UNION ALL
SELECT 2, 7, 'TypeA'

DECLARE @Product TABLE (pID INT, TypeA INT, TypeB INT, TypeC INT)

INSERT @Product
SELECT 1, 10, 20, 15 UNION ALL
SELECT 2, 8, 11, 10 UNION ALL
SELECT 3, 20, 30, 24

-- Show the expected output
SELECT b.pID,
theValue AS Price
FROM @Basket AS b
INNER JOIN (
SELECT pID, 'TypeA' AS theType, TypeA AS theValue FROM @Product UNION ALL
SELECT pID, 'TypeB', TypeB FROM @Product UNION ALL
SELECT pID, 'TypeC', TypeC FROM @Product
) AS d ON d.pID = b.pID AND d.theType = b.MemberType[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-31 : 16:39:06

As usual, I will be the guy who suggests that you should really re-design your database properly. You should have a ProductPrice table set up like this:


ProductID MemberType Price
--------- ---------- --------
1         TypeA      10
1         TypeB      20
1         TypeC      15
2         TypeA      8 
2         TypeB      11
2         TypeC      10
3         TypeA      20
3         TypeB      30
3         TypeC      24

The table would have a Primary key of ProductID/MemberType.

With that standard, normalized design, a simple JOIN is all you need to get the price. And now you can have unlimited types without worrying about altering your schema or your SQL code.

Good design = short, efficient code. Bad design = longer, slower, more convoluted code.

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-31 : 16:48:54

quote:
Originally posted by jsmith8858

As usual, I will be the guy who suggests that you should really re-design your database properly. You should have a ProductPrice table set up like this:

ProductID MemberType Price
--------- ---------- --------
1 TypeA 10
1 TypeB 20
1 TypeC 15
2 TypeA 8
2 TypeB 11
2 TypeC 10
3 TypeA 20
3 TypeB 30
3 TypeC 24

The table would have a Primary key of ProductID/MemberType.

With that standard, normalized design, a simple JOIN is all you need to get the price. And now you can have unlimited types without worrying about altering your schema or your SQL code.

Good design = short, efficient code. Bad design = longer, slower, more convoluted code.

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

I was going to get there after my earlier question ...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tentonipete
Starting Member

4 Posts

Posted - 2007-08-01 : 04:15:04

quote:
Originally posted by dinakar

Are those three the only types or could there be more?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Yes there will only be three types.

Unfortunately, like I said in my question, I would LOVE to design the database properly, but I didn't design the database and I am unable to change the table structure.

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE	@Basket TABLE (pID INT, quantity INT, MemberType SYSNAME)

INSERT	@Basket
SELECT	3, 4, 'TypeA' UNION ALL
SELECT	1, 2, 'TypeC' UNION ALL
SELECT	2, 7, 'TypeA'

DECLARE	@Product TABLE (pID INT, TypeA INT, TypeB INT, TypeC INT)

INSERT	@Product
SELECT	1, 10, 20, 15 UNION ALL
SELECT	2, 8, 11, 10 UNION ALL
SELECT	3, 20, 30, 24

-- Show the expected output
SELECT		b.pID,
		theValue AS Price
FROM		@Basket AS b
INNER JOIN	(
			SELECT	pID, 'TypeA' AS theType, TypeA AS theValue FROM @Product UNION ALL
			SELECT	pID, 'TypeB', TypeB FROM @Product UNION ALL
			SELECT	pID, 'TypeC', TypeC FROM @Product
		) AS d ON d.pID = b.pID AND d.theType = b.MemberType

E 12°55'05.25"
N 56°04'39.16"

Thanks for taking the time to help me out. I'm going to have to try and get this working for my data. I'm glad it is possible because I am having trouble working with the not normalised table structure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources