June 3, 2014 at 2:16 pm
Hi all
I have a Portfolio table as follows
DECLARE @Portfolio TABLE
(
PortFolioID VARCHAR(2),
TradeID VARCHAR(2),
ChildPortFolioID VARCHAR(2)
)
INSERT @Portfolio
SELECT 'P1','T1',NULL UNION ALL
SELECT 'P1',NULL,'P2' UNION ALL
SELECT 'P2','T2',NULL UNION ALL
SELECT 'P2','T3',NULL UNION ALL
SELECT 'P1','T4',NULL UNION ALL
SELECT 'P1',NULL,'P3' UNION ALL
SELECT 'P3','T5',NULL
I need output in following format
O/P
PortFolio Trade ChildPortfolio ChildTrade
P1 T1 NULL NULL
P1 T4 NULL NULL
P1 NULL P2 T2
P1 NULL P2 T3
P1 NULL P3 T5
I tired this query but don't know how to call it recursively.
SELECT * FROM @Portfolio
WHERE ChildPortFolioID IS NOT NULL
AND PortFolioID = 'P1'
UNION ALL
SELECT * FROM @Portfolio
WHERE TradeID IS NOT NULL
AND PortFolioID = 'P1'
We allow Mex 5 level deep hierarchy
Thanks
June 3, 2014 at 2:36 pm
That's an uncommon hierarchy structure. Usually the children have the reference to the parent and not the other way around (that reduces rows from the table).
Could you give an example with more than one child level?
June 3, 2014 at 2:51 pm
Luis Cazares (6/3/2014)
That's an uncommon hierarchy structure. Usually the children have the reference to the parent and not the other way around (that reduces rows from the table).Could you give an example with more than one child level?
I have seen it done this way before when you want to ensure that a parent can have no more than 1 child. It is a bit funky but it works the same...only backwards.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2014 at 2:53 pm
After adding following records
SELECT 'P2',NULL,'P4' UNION ALL
SELECT 'P4','T6',NULL
if i ask for portfolio P1's hierarchy
O/P should looks something like this
PortFolio Trade ChildPortfolio ChildTrade
P1 T1 NULL NULL
P1 T4 NULL NULL
P1 NULL P2 T2
P1 NULL P2 T3
P1 NULL P3 T5
P2 NULL P4 T6
June 3, 2014 at 3:14 pm
Sean Lange (6/3/2014)
Luis Cazares (6/3/2014)
That's an uncommon hierarchy structure. Usually the children have the reference to the parent and not the other way around (that reduces rows from the table).Could you give an example with more than one child level?
I have seen it done this way before when you want to ensure that a parent can have no more than 1 child. It is a bit funky but it works the same...only backwards.
I understand that scenario, but this is different. One parent can have multiple children.
June 3, 2014 at 3:17 pm
Luis Cazares (6/3/2014)
Sean Lange (6/3/2014)
Luis Cazares (6/3/2014)
That's an uncommon hierarchy structure. Usually the children have the reference to the parent and not the other way around (that reduces rows from the table).Could you give an example with more than one child level?
I have seen it done this way before when you want to ensure that a parent can have no more than 1 child. It is a bit funky but it works the same...only backwards.
I understand that scenario, but this is different. One parent can have multiple children.
Yeah it is rather unusual, especially with the additional rows. Not quite sure how to piece this one together.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2014 at 3:23 pm
This is an option. I'm not sure about it. I wonder if there's a better option with this structure. Would it be possible to make changes to the table?
DECLARE @PortfolioID varchar(2) = 'P1';
WITH rCTE AS(
SELECT *
FROM @Portfolio
WHERE PortFolioID = @PortfolioID
AND ChildPortFolioID IS NOT NULL
UNION ALL
SELECT p.*
FROM @Portfolio p
JOIN rCTE r ON p.PortFolioID = r.ChildPortFolioID
AND p.ChildPortFolioID IS NOT NULL
)
SELECT p.PortFolioID,
p.TradeID,
p.ChildPortFolioID,
CAST( NULL AS VARCHAR(2)) AS ChildTrade
FROM @Portfolio p
WHERE PortFolioID = @PortfolioID
AND ChildPortFolioID IS NULL
UNION ALL
SELECT r.PortFolioID,
r.TradeID,
p.PortFolioID,
p.TradeID
FROM rCTE r
JOIN @Portfolio p ON r.ChildPortFolioID = p.PortFolioID
WHERE p.TradeID IS NOT NULL;
EDIT: Remove wrong NOT
June 3, 2014 at 3:31 pm
This requirement is still is design phase an we are can change design of a table.
actual requirement is to retrieve data for multiple portfolio for reporting purpose.
June 4, 2014 at 8:32 am
This is just a suggestion and keep in mind that I don't have many details, but I'd usually work with something like this:
CREATE TABLE Portfolio(
PortFolioID char(2) NOT NULL,
ParentPortfolioID char(2) NULL,
CONSTRAINT PK_Portfolio PRIMARY KEY(PortFolioID),
CONSTRAINT FK_PortfolioParent FOREIGN KEY (ParentPortfolioID) REFERENCES Portfolio(PortFolioID));
CREATE TABLE PortfolioTrades(
PortFolioID char(2),
TradeID char(2),
CONSTRAINT PK_PortfolioTrades PRIMARY KEY(PortFolioID, TradeID),
CONSTRAINT FK_PortfolioTrades FOREIGN KEY (PortFolioID) REFERENCES Portfolio(PortFolioID));
INSERT Portfolio
SELECT 'P1', NULL UNION ALL
SELECT 'P2', 'P1' UNION ALL
SELECT 'P3', 'P1' UNION ALL
SELECT 'P4', 'P2';
INSERT PortfolioTrades
SELECT 'P1','T1' UNION ALL
SELECT 'P2','T2' UNION ALL
SELECT 'P2','T3' UNION ALL
SELECT 'P1','T4' UNION ALL
SELECT 'P3','T5' UNION ALL
SELECT 'P4','T6';
DECLARE @PortfolioID char(2) = 'P1';
WITH rCTE AS(
SELECT PortFolioID,
ParentPortfolioID
FROM Portfolio
WHERE PortFolioID = @PortfolioID
UNION ALL
SELECT p.PortFolioID,
p.ParentPortfolioID
FROM Portfolio p
JOIN rCTE r ON p.ParentPortfolioID = r.PortFolioID
)
SELECT ISNULL( r.ParentPortfolioID, r.PortFolioID) AS Portfolio,
CASE WHEN r.PortFolioID = @PortfolioID THEN pt.TradeID END AS Trade,
CASE WHEN r.PortFolioID <> @PortfolioID THEN r.PortFolioID END AS ChildPortfolio,
CASE WHEN r.PortFolioID <> @PortfolioID THEN pt.TradeID END AS Trade
FROM rCTE r
JOIN PortfolioTrades pt ON r.PortFolioID = pt.PortFolioID
ORDER BY ChildPortfolio;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply