SQL: Using Multi-Row data in Column

Question

This may not be possible in one query, but I'd like to see what my options are.

I have a large query that returns the data for each piece of an inventory (in this case trees). The query gets data from a few different tables. I mostly use left outer joins to bring this information in, so if it's not there I can ignore it and take the NULL. I have an interesting situation where a one-to-many relationship exists between "tree" and it's "pests".

tree table: treeID, treeHeight, etc....
pest (pest to tree) table: pestID, treeID, pestRef.....

I need a query that gets the top 6 pests for each tree and returns them as columns:

pest1, pest2, pest3... and so on.

I know that I could do this in multiple queries, however that would happen thousands of times just per use and our servers can't handle that.

Some notes: we're using ColdFusionMX7, and my knowledge of stored procedures is very low.

What flavour of SQL are you using? MySQL? SQL Server? Oracle? etc. — Siyual, Jul 01 '14 at 13:05
If you can figure out a way to get the data ordered by tree, you can re-arrange it with ColdFusion. — Dan Bracuk, Jul 01 '14 at 14:31
Maybe you should look at [PIVOT](http://technet.microsoft.com/en-us/library/ms177410%28v=sql.90%29.aspx) function. — Sergey Babrenok, Jul 01 '14 at 15:01

score 1 · Answer 1 · answered Jul 01 '14 at 15:23

One approach is to generate a column representing the pest rank by tree, then join the ranked pest table to the tree table with rank as a join condition. Make sure you use ROW_NUMBER not RANK because a tie would cause repeated numbers in RANK (but not ROW_NUMBER), and make sure you use LEFT OUTER joins so trees with fewer pests are not excluded. Also, I ordered by 2 conditions, but anything valid in a normal ORDER BY clause is valid here.

DECLARE @t TABLE (TreeID INT, TreeName VARCHAR(25));
DECLARE @p TABLE (PestID INT, TreeID INT, PestName VARCHAR(25));

INSERT INTO @t VALUES (1,'ash'),(2,'elm'),(3,'oak')
INSERT INTO @p VALUES (1,1,'ash borer'),(2,1,'tent catapilar'),(3,1,'black weevil'),(4,1,'brown weevil');
INSERT INTO @p VALUES (5,2,'elm thrip'),(6,2,'wooly adelgid');
INSERT INTO @p VALUES (7,3,'oak gall wasp'),(8,3,'asian longhorn beetle'),(9,3,'aphids');

WITH cteRankedPests as (
    SELECT PestID, TreeID, PestName, ROW_NUMBER() OVER (PARTITION BY TreeID ORDER BY PestName,PestID) as PestRank
    FROM @p 
)
SELECT T.TreeID, T.TreeName 
    , P1.PestID as P1ID, P1.PestName as P1Name
    , P2.PestID as P2ID, P2.PestName as P2Name
    , P3.PestID as P3ID, P3.PestName as P3Name
FROM @t as T 
    LEFT OUTER JOIN cteRankedPests as P1 ON T.TreeID = P1.TreeID AND P1.PestRank = 1
    LEFT OUTER JOIN cteRankedPests as P2 ON T.TreeID = P2.TreeID AND P2.PestRank = 2
    LEFT OUTER JOIN cteRankedPests as P3 ON T.TreeID = P3.TreeID AND P3.PestRank = 3

Just looking at this I can see it working. I was on a deadline so I ended up making a second query per tree and getting the pests back as an array. And using ColdFusion to push those into the results the the original query. Dirty, ugly, slow... I'll give this a shot when I get some time. — henonChesser, Jul 02 '14 at 18:28

SQL: Using Multi-Row data in Column

1 Answers1