How to SELECT a specifc column based on the content of a field of a joined table

Question

Post reply

How to SELECT a specifc column based on the content of a field of a joined table

sylvain.lambert

SSC Rookie

Points: 28
More actions
April 15, 2010 at 2:28 pm

#220495

Hi,
Lets figure 2 tables, simplified from the real case, with inserts to create dummy data:
-- Describe a house
CREATE TABLE #Main (NoID int, Area float, NbRooms int, NbGarage int)
-- Values for part of the house
CREATE TABLE #PartValue (PartName varchar(25), PartValue float)
INSERT #Main VALUES (1, 150, 5, 1)
INSERT #Main VALUES (2, 170, 4, 0)
INSERT #Main VALUES (3, 180, 6, 2)
INSERT #PartValue VALUES ('Area', 50)
INSERT #PartValue VALUES ('NbRooms',100)
INSERT #PartValue VALUES ('NbGarage',1000)
The following SELECT is what would like to improve:
SELECT m.NoID, p.PartName,
(SELECT CASE WHEN p.PartName='Area' THEN Area
WHEN p.PartName='NbRooms' THEN NbRooms
WHEN p.PartName='NbGarage' THEN NbGarage END AS Content
FROM #Main WHERE NoID=m.NoID) PartSize,
p.PartValue,
(SELECT CASE WHEN p.PartName='Area' THEN Area
WHEN p.PartName='NbRooms' THEN NbRooms
WHEN p.PartName='NbGarage' THEN NbGarage END AS Content
FROM #Main WHERE NoID=m.NoID)*p.PartValue AS PartTotal
FROM #Main m
CROSS JOIN #PartValue p
ORDER BY NoID
I need to replace the CASE statement with something dynamic that will use p.PartName to retrieve the value from #Main, something like what we can in VBA with a recordset like #Main(p.PartName) or m(p.PartName)
I hope I'm clear enough, I don't know if a solution exist, thanks for your help.
Sylvain

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Based on the sample data you provided, what are the expected results?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GSquared SSC Guru Points: 260824 More actions · Answer 2

You'll either need to predefine the value-column combinations, or use dynamic SQL, or use a name-value pair table.

If values will be turned into columns in a dynamic manner, you need to take a good look at your data model and see if you can improve it. Otherwise, you'll be constantly adding new columns to your tables. That applies to the first two options. Either one ends up being problematic the first time someone misspells "Area".

If you go with name-value, expect the database to be a bit slow and a bit hard to program against, but it will have the kind of flexibility that allows you to define new "columns" without having to modify the actual database structure.

You'll need to decide which version will work best for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sylvain.lambert SSC Rookie Points: 28 More actions · Answer 3

The expected result would be:

NoIDPartNamePartSizePartValuePartTotal

1Area150507500

1NbRooms5100500

1NbGarage110001000

2Area170508500

2NbRooms4100400

2NbGarage010000

3Area180509000

3NbRooms6100600

3NbGarage210002000

WayneS SSC Guru Points: 95461 More actions · Answer 4

I would suggest that you replace the #Main table with this:

CREATE TABLE #HouseRooms (NoID int, RoomName varchar(25), RoomValue float)

This allows you to run this:

-- Your original tables / code here

if object_id('tempdb..#Main') IS NOT NULL DROP TABLE #Main

if object_id('tempdb..#PartValue') IS NOT NULL DROP TABLE #PartValue

CREATE TABLE #Main (NoID int, Area float, NbRooms int, NbGarage int)

-- Values for part of the house

CREATE TABLE #PartValue (PartName varchar(25), PartValue float)

INSERT #Main VALUES (1, 150, 5, 1)

INSERT #Main VALUES (2, 170, 4, 0)

INSERT #Main VALUES (3, 180, 6, 2)

INSERT #PartValue VALUES ('Area', 50)

INSERT #PartValue VALUES ('NbRooms',100)

INSERT #PartValue VALUES ('NbGarage',1000)

-- get the original results

;WITH CTE AS (

SELECT m.NoID,

p.PartName,

PartSize = CASE WHEN p.PartName = 'Area' THEN m.Area

WHEN p.PartName = 'NbRooms' THEN m.NbRooms

WHEN p.PartName = 'NbGarage' THEN m.NbGarage

END,

p.PartValue

FROM #Main m

CROSS JOIN #PartValue p

)

SELECT NoID,

PartName,

PartSize,

PartValue,

PartTotal = PartSize * PartValue

FROM CTE

ORDER BY NoID

I'd suggest adding this new table. Your code then would be this

(Note: run both sections of code together)

-- I suggest you change the #Main table to this

if object_id('tempdb..#HouseRooms') IS NOT NULL DROP TABLE #HouseRooms

CREATE TABLE #HouseRooms (NoID int, RoomName varchar(25), RoomValue float)

-- I'd suggest putting a FOREIGN KEY CONSTRAINT

-- between this table and #PartValue

-- populate it with the data from #Main

INSERT INTO #HouseRooms

SELECT NoID, 'Area', Area FROM #Main UNION ALL

SELECT NoID, 'NbRooms', NbRooms FROM #Main UNION ALL

SELECT NoID, 'NbGarage', NbGarage FROM #Main

-- now, get the same results

SELECT h.NoID,

p.PartName,

PartSize = h.RoomValue,

PartValue,

PartTotal = p.PartValue * h.RoomValue

FROM #HouseRooms h

JOIN #PartValue p

ON h.RoomName = p.PartName

ORDER BY h.NoID, h.RoomName

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sylvain.lambert SSC Rookie Points: 28 More actions · Answer 5

Having the possibility to convert my Main table into a vertical structure like you suggest would be the best I agree but I can't right now. It is part of a complex system and I don't have the time and budget to revisit it right now. I have to work with what I have, a table with named column. Which actually have more than 1 millions records with more than 200 columns!

Sorry, I should have precised that I'm stuck with the actual structure.

Any idea?

WayneS SSC Guru Points: 95461 More actions · Answer 6

Just gotta love it... you're query is taking denormalized data and normalizing it.... and you'd rather keep it this way :w00t:

With 200 columns, you're looking at dynamically performing an UNPIVOT operation on your data. See PIVOT/UNPIVOT in BOL for how to do this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sylvain.lambert SSC Rookie Points: 28 More actions · Answer 7

While I used PIVOT for years, there is little brother UNPIVOT that I never looked at! It's still not perfect while I have to list all the column name in the table but better than what I had.

Much easier to programaticcaly generate the needed SQL code and execute it.

Thanks for your help. 🙂

WayneS SSC Guru Points: 95461 More actions · Answer 8

Yep, it's not perfect. It would be much better to normalize that data... then data can point to data, and not to columns.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2