How to make hierarchical data "lay flat" using SQL

tinlong · July 31st, 2003, 09:15 PM

Hi all, I'm a C++/C# programmer, nice to meet you all! Now I would like to ask a question. Can anyone answer me?

The question is like this: suppose I have a table:

CREATE TABLE HavingFruit
(
PersonID int,
Fruit nvarchar(10)
);

PersonID is a foreign key towards a "person" table. It's a simple table, so it does not have any tricky stuff. Every person in the "person" table can have zero to infinity entries here. So when query this table, it would be like:

PersonID Fruit
-------- ----------
111 Apple
111 Orange
111 Banana
111 Pear
113 Rasberry
115 Pineapple
115 Cherry
.....

Now, I want to have a view, so that it looks like this:

PersonID Fruit1 Fruit2 Fruit3
-------- ---------- ---------- ----------
111 Apple Orange Banana
113 Rasberry NULL NULL
115 Pineapple Cherry NULL
....

The point is, I would like to have a view which makes the hierarchical table "flat", to display up to 3 results for one person in one single row.

I request this with a reason: I have a custom report as a project, which can let users choose, in this case, input, output or sort using Fruit1, Fruit2 or Fruit3 individually. I could obviously do it in C#, however I was told to make the architecture as generic as possible. So I want to make most of the data regrouping and filtering in SQL Server. That's exactly why I want a view, because I could do that in stored procedure (unfortunately the only way I know to accomplish this is using cursor, which I told is evil), but then I still have to filter the data in C#, which increases the complexity of the existing ASP.NET C# code.

Thanks for anyone's concern, and thanks in advance for any answer.

David Cameron · August 1st, 2003, 01:38 AM

Nice to meet another C++ programmer. Not that I acutally get to write C++ nowadays, except in my own time for 'fun'.

I delayed answering this because I was hoping that someone else was going to pipe up. Sigh. There are no nice ways to do this. Off the top of my head the only one I can think of is to use correlated subqueries:

Code:

SELECT PersonID,
    (SELECT TOP 1 Fruit
     FROM HavingFruit
     WHERE PersonID = HF.PersonID
     ORDER BY Fruit DESC) AS Fruit1,
    (SELECT TOP 1 Fruit
     FROM (SELECT TOP 2 Fruit
           FROM HavingFruit
           WHERE PersonID = HF.PersonID
           ORDER BY Fruit Desc)
     ORDER BY Fruit Asc
    ) AS Fruit2,
    (SELECT TOP 1 Fruit
     FROM (SELECT TOP 3 Fruit
           FROM HavingFruit
           WHERE PersonID = HF.PersonID
           ORDER BY Fruit Desc)
     ORDER BY Fruit Asc
    ) AS Fruit3,
    (SELECT TOP 1 Fruit
     FROM (SELECT TOP 4 Fruit
           FROM HavingFruit
           WHERE PersonID = HF.PersonID
           ORDER BY Fruit Desc)
     ORDER BY Fruit Asc
    ) AS Fruit4
FROM HavingFruit HF

Looks pretty ugly to me and has some problems.

1. Performance. Not only is the resultset basically derived from subqueries, but some of the subqueries use derived tables. Performance will be hideous. If you go down this path make sure have have a clustered index on PersonID and a nonclustered index on Fruit.

2. Scalability. You need to have as many subqueries as you have different types of fruit. This would get hard to maintain.

I tried to think up something creative using CASE, temp tables etc and came up blank.

The only other thing I can think of is that there is an undocumented SQL Server feature that allows you to flatten a resultset into CSV values. There is an example of it here:
http://vyaskn.tripod.com/code/cat.txt

I don't know if it could be adapted to your circumstances. I try to avoid undocumnted features where possible.

regards
David Cameron