March 13, 2014 at 4:46 am
Hi!
I need tree, Level and path from the below two tables;
--========First Table_1
CREATE TABLE [dbo].[Table_1](
[MTID] [int] IDENTITY(1,1) NOT NULL,
[Item] [nvarchar](100) NULL,
[Type] [smallint] NULL
) ON [PRIMARY]
--========Second Table_2
CREATE TABLE [dbo].[Table_2](
[ID] [int] IDENTITY(101,1) NOT NULL,
[Item] [nvarchar](100) NULL,
[Type] [smallint] NULL,
[SID] [int] NOT NULL,
[MID] [int] NOT NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[MID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--==========Insert Data into Table_1
INSERT INTO Table_1 (Item, Type)
SELECT 'Item 1', 1 UNION ALL
SELECT 'Item 2', 1 UNION ALL
SELECT 'Item 3', 1 UNION ALL
SELECT 'Item 4', 1 UNION ALL
SELECT 'Item 5', 1 UNION ALL
SELECT 'Item 6', 1 UNION ALL
SELECT 'Item 7', 1 UNION ALL
SELECT 'Item 8', 1 UNION ALL
SELECT 'Item 9', 1 UNION ALL
SELECT 'Item 10', 1 UNION ALL
SELECT 'Profile 1', 2 UNION ALL
SELECT 'Profile 2', 2 UNION ALL
SELECT 'Profile 3', 2 UNION ALL
SELECT 'Package 1', 3 UNION ALL
SELECT 'Package 2', 3 UNION ALL
SELECT 'Package 3', 3
--==========Insert Data into Table_2
INSERT INTO Table_2 (Item, Type, SID, MID)
SELECT 'Item 1', 1, 1, 11 UNION ALL
SELECT 'Item 2', 1, 2, 11 UNION ALL
SELECT 'Item 3', 1, 3, 12 UNION ALL
SELECT 'Item 4', 1, 4, 12 UNION ALL
SELECT 'Item 5', 1, 5, 13 UNION ALL
SELECT 'Item 6', 1, 6, 13 UNION ALL
SELECT 'Profile 1', 2, 11, 14 UNION ALL
SELECT 'Item 7', 1, 7, 14 UNION ALL
SELECT 'Profile 2', 2, 12, 15 UNION ALL
SELECT 'Item 8', 1, 8, 15 UNION ALL
SELECT 'Package 1', 3, 14, 16 UNION ALL
SELECT 'Package 2', 3, 15, 16 UNION ALL
SELECT 'Profile 3', 2, 13, 16 UNION ALL
SELECT 'Item 9', 1, 9, 16
--Tree Structure
Item 1
Item 2
Item 3
Item 4
Item 5
Item 6
Item 7
Item 8
Item 9
Item 10
Profile 1
--|Item 1
--|Item 2
Profile 2
--|Item 3
--|Item 4
Profile 3
--|Item 5
--|Item 6
Package 1
--|Profile 1
----|Item 1
----|Item 2
--|Item 7
Package 2
--|Profile 2
----|Item 3
----|Item 4
--|Item 8
Package 3
--|Package 1
----|Profile 1
------|Item 1
------|Item 2
----|Item 7
--|Package 2
----|Profile 2
------|Item 3
------|Item 4
----|Item 8
--|Profile 3
----|Item 5
----|Item 6
--|Item 9
If anyone could provide a solution for the sample Data then it would be very helpful and would give me a push in the right direction to implement the same on the actual Data that I have. Thanks in advance
Imdad Khan
March 13, 2014 at 8:38 am
Nice job posting ddl and consumable sample data. That goes a long way towards getting an answer. I don't see anything in your code or your desired that makes this a tree.
Does this work? It seems to match what you posted as desired output.
select *
from Table_1 t1
left join Table_2 t2 on t2.MID = t1.MTID
_______________________________________________________________
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/
March 14, 2014 at 6:21 am
Thaks SSChampion for reply.
I solved it using Recursive CTE. This is the solution:
WITH tree(ID, ITEM, PID, LEVEL, TreePath, FilePath)
AS(
SELECT MTID, Item, 0 AS PID, 0 AS LEVEL, CAST(MTID AS VARCHAR(1024)) AS TreePath, CAST(ITEM AS VARCHAR(1024)) AS FilePath FROM Table_1
UNION ALL
SELECT b.SID, b.ITEM, b.MID, tree.LEVEL + 1 AS LEVEL,
CAST(tree.TreePath + '|' + CAST(b.SID AS VARCHAR(1024)) AS VARCHAR(1024)) AS TreePath
,CAST(tree.FilePath + ' -> ' + CAST(b.item AS VARCHAR(1024)) AS VARCHAR(1024)) AS FilePath
FROM TABLE_2 b
INNER JOIN tree
ON tree.ID = b.MID
)
SELECT ID, ITEM, PID, LEVEL, TreePath, FilePath FROM tree
ORDER BY FilePath
Hope this helps others, like me, as well.:cool:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply