Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 31st, 2003, 09:15 PM
Registered User
 
Join Date: Jul 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to tinlong Send a message via MSN to tinlong
Default How to make hierarchical data "lay flat" using SQL

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.
 
Old August 1st, 2003, 01:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
flat vs. hierarchical style asker XML 0 March 28th, 2007 06:16 AM
transform from flat to hierarchical kkt XSLT 5 November 27th, 2006 02:15 AM
flat XML to hierarchical XML eduijs XSLT 1 April 28th, 2006 05:43 AM
From flat to hierarchical seesharper XSLT 2 February 5th, 2006 06:48 AM
translating a flat xml to a hierarchical xml stevea XSLT 4 June 13th, 2005 05:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.