Horisontal to vertical list - 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 2000 Forums

SQL Server Development (2000)

Horisontal to vertical list

Author

Topic

de9625
Starting Member

17 Posts

Posted - 2001-12-11 : 09:48:23

I want to display an ordinary list.
I have a table with four fields:
Name, alt_1, alt_2, alt_3
the three last fields contain the type of info. just alternative values.

And a second table with the description of the alternatives:
alt, description

The list I want to get should look like this:
Name Alt Description
-----------------------------
xxx asd desc
xxx fgf desc
yyy asd desc

etc...

This is my solution today but I dont like it!

SELECT Name, alt_1 AS alt, description
FROM table_1 t1
LEFT OUTER JOIN table_2 t2
ON t1.alt_1 = t2.alt
WHERE t1.fieldID = "WhatEver"
UNION
SELECT fieldID, name, alt_2 AS alt, description
FROM table_1 t1
LEFT OUTER JOIN table_2 t2
ON t1.alt_2 = t2.alt
WHERE t1.fieldID = "WhatEver"
UNION
...and the same for the last alt_3

This will give me a list like I want it but this solution doesent seem to be very efficient if you add a couple of alteratives. Im not in the position to change the design of the tables so dont bother to redesign the tables. I have done some simplifications to the tables but I hope that the point hasnt been lost.

Edited by - de9625 on 12/11/2001 10:06:41

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-11 : 10:23:30

Field names that end in a number larger than 2 are always wrong.

/2

If they came from separate columns in the input and have to end up in separate rows in the output then you're going to do multiple passes on the table to get the 3 sets of values. So no, there's nothing that's fundamentally better than the way you're doing it.
You can fiddle around with unioning them first and then doing the JOIN and WHERE, but it's only syntax, so there's no guarantee it will make the query plan any different let alone better.


SELECT  Name, alt, description  
FROM (
  SELECT fieldID, name, alt_1 AS alt FROM table_1
  UNION SELECT fieldID, name, alt_2 FROM table_1
  UNION SELECT fieldID, name, alt_3 FROM table_1) t1
LEFT OUTER JOIN table_2 t2 ON t1.alt = t2.alt
WHERE t1.fieldID = "WhatEver"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources