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.

Answer 1

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