January 6, 2017 at 12:15 pm
Hi,
I want to create a flat table as shown in the desired results using the temp table.
CREATE TABLE #MyTestTable
(
Title NVARCHAR(200),
ID NVARCHAR(100),
ChildID NVARCHAR(650)
)
INSERT INTO #MyTestTable ( Title,ID,ChildID)
SELECT 'Root','IEABEL6II7777777','XY0000000KIE'
UNION
SELECT 'Root Project','XY0000000KIE','XY0000000KIA'
UNION
SELECT 'Root Project','XY0000000KIE','XY0000000KIB'
UNION
SELECT 'Sales Workflow','XY0000000KIA','XY0000000LLA'
UNION
SELECT 'Sales Workflow','XY0000000KIA','XY0000000LLB'
UNION
SELECT 'Sales Project1','XY0000000LLA','XY0000000LLAX'
UNION
SELECT 'Sales Project2','XY0000000LLB','XY0000000LLAY'
UNION
SELECT 'Sales SubProject1','XY0000000LLAX','XY0000000LLAX-1'
UNION
SELECT 'Sales Attribute1','XY0000000LLAX-1',''
UNION
SELECT 'Sales SubProject2','XY0000000LLAY','XY0000000LLAD'
UNION
SELECT 'Sales Attribute2','XY0000000LLAD','XY0000000LLAD-1'
UNION
SELECT 'Sales Attribute1','XY0000000LLAX-1',''
UNION
SELECT 'Technology Workflow','XY0000000KIB','XY0000000TECH1'
UNION
SELECT 'Technology Project1','XY0000000TECH1','XY0000000SUB1'
UNION
SELECT 'Technology SubProject1','XY0000000SUB1','XY0000000ATTR1'
UNION
SELECT 'Technology Attribute1','XY0000000ATTR1',''
I need a flat table with structure like below using above temp table
--Desired results only from (Root Project) only
SELECT 'Root Project' AS ProjectRoot,'XY0000000KIE' AS ParentID,'XY0000000KIA' AS WorkFlowID , 'Sales Workflow' As Workflow,'Sales Project1' AS Project ,'XY0000000LLA' AS ProjectID,'XY0000000LLAX' AS SubProjectID,'Sales SubProject1' AS SubProjects,'XY0000000LLAX-1' AS AttributeID,'Sales Attribute1' AS Attributes
DROP TABLE #MyTestTable
Please help.
January 6, 2017 at 12:27 pm
Great job posting ddl, sample data and desired output. What I don't understand at all is how you get from your data to the output. The column names don't seem to help and you have lots of rows with the contents for the values. It seems like a sort of dynamic cross tab is needed here but I can't make heads or tails of the output.
_______________________________________________________________
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/
January 6, 2017 at 12:53 pm
Something like this?
SELECT
pr.Title AS ProjectRoot , pr.ID AS ParentID,
wf.Title AS WorkFlow , wf.ID AS WorkflowID,
p.Title AS Project , p.ID AS ProjectID,
sp.Title AS SubProject , sp.ID AS SubProjectsID,
pa.Title AS Attribute , pa.ID AS AttributesID
FROM #MyTestTable r
JOIN #MyTestTable pr ON r.ChildID = pr.ID
JOIN #MyTestTable wf ON pr.ChildID = wf.ID
JOIN #MyTestTable p ON wf.ChildID = p.ID
JOIN #MyTestTable sp ON p.ChildID = sp.ID
JOIN #MyTestTable pa ON sp.ChildID = pa.ID
--WHERE SomeCondition = @SomeArgument
;
January 6, 2017 at 1:13 pm
Thanks Luis. It work perfectly .
If I have to add another level (Attribute2 and AttributeID2) how can I achieve it ?
I tried adding JOIN #MyTestTable pa2 ON sp.ChildID = pa2.ID to the end of the join but it didn't work .
January 6, 2017 at 1:22 pm
Luis Cazares (1/6/2017)
Something like this?
SELECT
pr.Title AS ProjectRoot , pr.ID AS ParentID,
wf.Title AS WorkFlow , wf.ID AS WorkflowID,
p.Title AS Project , p.ID AS ProjectID,
sp.Title AS SubProject , sp.ID AS SubProjectsID,
pa.Title AS Attribute , pa.ID AS AttributesID
FROM #MyTestTable r
JOIN #MyTestTable pr ON r.ChildID = pr.ID
JOIN #MyTestTable wf ON pr.ChildID = wf.ID
JOIN #MyTestTable p ON wf.ChildID = p.ID
JOIN #MyTestTable sp ON p.ChildID = sp.ID
JOIN #MyTestTable pa ON sp.ChildID = pa.ID
--WHERE SomeCondition = @SomeArgument
;
Well done sir. You win the internet today....or perhaps we know how has the crystal ball this week. 😀
_______________________________________________________________
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/
January 6, 2017 at 1:34 pm
I could it get it working ..
SELECT
pr.Title AS ProjectRoot , pr.ID AS ParentID,
wf.Title AS WorkFlow , wf.ID AS WorkflowID,
p.Title AS Project , p.ID AS ProjectID,
sp.Title AS SubProject , sp.ID AS SubProjectsID,
pa.Title AS Attribute , pa.ID AS AttributesID,
pa2.Title AS Attribute2 , pa2.ID AS AttributesID2
FROM #MyTestTable r
JOIN #MyTestTable pr ON r.ChildID = pr.ID
JOIN #MyTestTable wf ON pr.ChildID = wf.ID
JOIN #MyTestTable p ON wf.ChildID = p.ID
JOIN #MyTestTable sp ON p.ChildID = sp.ID
JOIN #MyTestTable pa ON sp.ChildID = pa.ID
LEFT JOIN #MyTestTable pa2 ON pa.ChildID = pa2.ID
Thanks for your help again . 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply