开发者

Pivoting Data in Multiple Rows to a Single Column

开发者 https://www.devze.com 2023-03-12 07:35 出处:网络
I have a database table with categories for different products in it.Each category has only one parent and each product can have multiple categories.I need to build a sort of breadcrumb navigation开发

I have a database table with categories for different products in it. Each category has only one parent and each product can have multiple categories. I need to build a sort of breadcrumb navigation开发者_JAVA百科 for a data migration, but can't figure out how to map the data so that I don't have tonds of queries.

The database is setup like so:

id     category              sort     parent
1      Home                  0        0
58     Car & Truck           4        1
135    10' Wide Shelters     0        58

Now I need to get the data back like this: Home/Car & Truck/10' Wide Shelters.

Is this possible with pure SQL or do I need to mix-in ColdFusion to get it. If I can do this with pure SQL then how would I do that, and if I can't then what would the ColdFusion look like?


Other RDBMSes support various ways of solving this problem (recursive with in ANSI SQL, connect by in Oracle, etc). But in MySQL, you're pretty much left with nested sets.


Using nested sets, as recommended by user349433 I was able to come up with this query

SELECT c1.id AS id1, c1.category AS name1, c2.id AS id2, c2.category AS name2, c3.id AS id3, c3.category AS name3, c4.id AS id4, c4.category AS name4, c5.id AS id5, c5.category AS name5
FROM category c1
LEFT JOIN category AS c2 ON c2.parentid = c1.id
LEFT JOIN category AS c3 ON c3.parentid = c2.id
LEFT JOIN category AS c4 ON c4.parentid = c3.id
LEFT JOIN category AS c5 ON c5.parentid = c4.id

My data never goes deeper than 5 levels (I verified by checking with c6 and getting nulls). From here I can query against this

SELECT (name1 + '/' + name2 + '/' + name3 + '/' + name4 + '/' + name5) AS category
FROM getCats
WHERE
    <cfloop query="Arguments.assignments">
        (id1 = #Arguments.assignments.categoryid# OR id2 = #Arguments.assignments.categoryid# OR id3 = #Arguments.assignments.categoryid# OR id4 = #Arguments.assignments.categoryid# OR id5 = #Arguments.assignments.categoryid#)
        <cfif Arguments.assignments.currentrow IS NOT Arguments.assignments.recordCount> OR </cfif>
    </cfloop>

I already had a query of products and their category ids to loop over.

0

精彩评论

暂无评论...
验证码 换一张
取 消