February 18, 2010 at 6:04 am
I am currently preparing hierarchical data for upload into an Essbase olap database. The hierarchical data structure represents some of the products of my company. Due to the limitaions of the version of Essbase and business rules the hierarchical data structure has to be in a certain format. These are:
1) hierachy cannot be more than 20 levels deep
2) The first and most left product in structure needs to be selected, Same product codes later in file or at equal/lower position in structure need to be removed.
For example The product structure is of the format 'xxx yyy zzz....etc'
Every xxx represents a product code. A total of 40 product codes can be put into the structuurcode. Repeating of productcodes is possible.
Like BT1 DP2 DP2 DP2 DP2 XL1
For upload into Essbase this structure would have to be BT1 DP2 XL1
Therefore my question is how can I alter my structure with T-SQL such that first and most left product in structure is selected and its occurance elsewhere is ignored.
I have some sample sql to elaborate the scenario:
CREATE TABLE #Product
(
category_id INT,
name VARCHAR(20) NOT NULL,
LEVEL INT DEFAULT NULL,
STRUCTURE VARCHAR(20) NOT NULL)
INSERT INTO #Product(category_id,name,LEVEL,STRUCTURE)
select 1,'BT1',0,'BT1'
UNION ALL
SELECT 2,'DP2',1,'BT1 DP2'
UNION ALL
SELECT 3,'DP2',2,'BT1 DP2 DP2'
UNION ALL
SELECT 4,'DP3',2,'BT1 DP2 DP3'
UNION ALL
SELECT 5,'DP4',2,'BT1 DP2 DP4'
UNION ALL
SELECT 6,'SP2',1 ,'BT1 SP2'
UNION ALL
SELECT 7,'XR5',3,'BT1 DP2 DP4 XR5'
UNION ALL
SELECT 8,'FG6',3,'BT1 DP2 DP3 FG6'
UNION ALL
SELECT 9,'XR7',4,'BT1 DP2 DP3 FG6 XR7'
UNION ALL
SELECT 10,'XR8',4,'BT1 DP2 DP3 FG6 XR8'
In the above scenario record 3 should not be included as Product DP2 is found at both level 2 and level 1
Any suggestions would be very much appreciated
February 18, 2010 at 1:34 pm
I would use a split string function (for a sample please have a look at the Tally table link referenced in my signature) to get the data in a relational model and work from there. Something along the following lines (side note: I did note include the definition of the split string function I'm using).
;WITH cte AS -- transform STRUCTURE into a realtional table
(
SELECT category_id,item
FROM #Product
CROSS APPLY dbo.DelimitedSplit(STRUCTURE,' ')
),
cte2 AS -- select the category with an element being referenced more than once within a category_id
(
SELECT category_id
FROM cte
GROUP BY category_id,item
HAVING(COUNT(*)>1)
)
SELECT #Product.* -- final output
FROM #Product
LEFT OUTER JOIN cte2
ON #Product.category_id = cte2.category_id
WHERE cte2.category_id IS NULL
February 19, 2010 at 4:03 am
Many thanks for that, it seems to work. If i was to do it in sql 2k this would not be possible as CTEs cannot be used right? If this is the case how would It be done in SQL 2k as I may have to do this in a sql2K environment.
February 19, 2010 at 10:03 am
A CTE (at least as it is used in this case) is nothing but a different kind of writing subqueries.
Note: I used the same alias for the subqueries as I did for the CTEs so you can compare it easily.
SELECT #Product.* -- final output
FROM #Product
LEFT OUTER JOIN
( SELECT category_id
FROM
(
SELECT category_id,item
FROM #Product
CROSS APPLY dbo.DelimitedSplit(STRUCTURE,' ')
)cte
GROUP BY category_id,item
HAVING(COUNT(*)>1)
)cte2
ON #Product.category_id = cte2.category_id
WHERE cte2.category_id IS NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply