May 28, 2015 at 8:44 pm
I wish I had more time to work on this but as usual everything needs to be done asap. This seems pretty tricky.
Admittedly, I have not been the best about posting data per Jeff's very helpful Etiquette article. (http://www.sqlservercentral.com/articles/Best+Practices/61537/).
So, this time around I am going to change that!
We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.
Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department
If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data
--===== Create the test table with
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Test_Data
(
[Entity] [nvarchar](255) NULL,
[IndentLevel] [float] NULL
)
--===== Insert the test data into the test table
INSERT INTO #Test_Data
(Entity,IndentLevel)
SELECT 'Lockheed Martin Corp','1' UNION ALL
SELECT 'Skunk Works - Roswell,NM','2' UNION ALL
SELECT 'LM Aviation','3' UNION ALL
SELECT 'Recon','4' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 500 - Admin','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 501 - Tech','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 502 - Clerical','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 503 - Acct','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 504 - HR','5' UNION ALL
SELECT 'Fighter','4' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 505 - Ammunition','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 506 - Stuff','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 507 - More Stuff','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 508 - Cat5 Cables','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 509 - Mops','5' UNION ALL
SELECT 'Bombers','4' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 510 - Wings','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 511 - HUD','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 512 - Radar','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 513 - Blackberry Phones','5' UNION ALL
SELECT 'Deep 6 - Norfolk, VA','2' UNION ALL
SELECT 'LM - Submarines','3' UNION ALL
SELECT 'Virginia Class Subs','4' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 514 - Water','5' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 515- Reactors','5' UNION ALL
SELECT 'Ohio Class Subs','4' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 516 - Torpedos','5' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 517 - Counter Measures','5' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 518 - Bang and Olufsen party speakers','5'
GO
SELECT * FROM #Test_Data
-- END CURRENT DATA SAMPLE
So with the data above, is it possible to get to this result?
IF OBJECT_ID('TempDB..#Results','U') IS NOT NULL
DROP TABLE #Results
--===== Create the test table with
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Results
(
[SYSTEM] [nvarchar](255) NULL,
[FACILITY] [nvarchar](255) NULL,
[SERVICE_LINE] [nvarchar](255) NULL,
[DIVISION] [nvarchar](255) NULL,
[DEPARTMENT] [nvarchar](255) NULL
)
--===== Insert the test data into the test table
INSERT INTO #Results
([SYSTEM],FACILITY,SERVICE_LINE,DIVISION,DEPARTMENT)
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Recon','Facility 1 - LM Cost Center: 500 - Admin' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Recon','Facility 1 - LM Cost Center: 501 - Tech' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Recon','Facility 1 - LM Cost Center: 502 - Clerical' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Recon','Facility 1 - LM Cost Center: 503 - Acct' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Recon','Facility 1 - LM Cost Center: 504 - HR' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Fighter','Facility 1 - LM Cost Center: 505 - Ammunition' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Fighter','Facility 1 - LM Cost Center: 506 - Stuff' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Fighter','Facility 1 - LM Cost Center: 507 - More Stuff' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Fighter','Facility 1 - LM Cost Center: 508 - Cat5 Cables' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Fighter','Facility 1 - LM Cost Center: 509 - Mops' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Bombers','Facility 1 - LM Cost Center: 510 - Wings' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Bombers','Facility 1 - LM Cost Center: 511 - HUD' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Bombers','Facility 1 - LM Cost Center: 512 - Radar' UNION ALL
SELECT 'Lockheed Martin Corp','Skunk Works - Roswell,NM','LM Aviation','Bombers','Facility 1 - LM Cost Center: 513 - Blackberry Phones' UNION ALL
SELECT 'Lockheed Martin Corp','Deep 6 - Norfolk, VA','LM - Submarines','Virginia Class Subs','Facility 2 - LM Cost Center: 514 - Water' UNION ALL
SELECT 'Lockheed Martin Corp','Deep 6 - Norfolk, VA','LM - Submarines','Virginia Class Subs','Facility 2 - LM Cost Center: 515- Reactors' UNION ALL
SELECT 'Lockheed Martin Corp','Deep 6 - Norfolk, VA','LM - Submarines','Ohio Class Subs','Facility 2 - LM Cost Center: 516 - Torpedos' UNION ALL
SELECT 'Lockheed Martin Corp','Deep 6 - Norfolk, VA','LM - Submarines','Ohio Class Subs','Facility 2 - LM Cost Center: 517 - Counter Measures' UNION ALL
SELECT 'Lockheed Martin Corp','Deep 6 - Norfolk, VA','LM - Submarines','Ohio Class Subs','Facility 2 - LM Cost Center: 518 - Bang and Olufsen party speakers'
GO
SELECT *
FROM #Results
Thank you!
May 28, 2015 at 8:48 pm
Interesting. Server time is UTC +1. Just noticed that. Africa?
May 28, 2015 at 9:30 pm
The only part you're missing is a means to maintain the hierarchy/order once the data is inserted into the table.
You'll noticed that I added an IDENTITY column to the #Test_Data to fix the issue...
In any case, give this a look and make sure it's giving you what you need..
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data
--===== Create the test table with
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Test_Data
(
RowOrder INT NOT NULL IDENTITY(1,1),
[Entity] [nvarchar](255) NULL,
[IndentLevel] [float] NULL
)
--===== Insert the test data into the test table
INSERT INTO #Test_Data
(Entity,IndentLevel)
SELECT 'Lockheed Martin Corp','1' UNION ALL
SELECT 'Skunk Works - Roswell,NM','2' UNION ALL
SELECT 'LM Aviation','3' UNION ALL
SELECT 'Recon','4' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 500 - Admin','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 501 - Tech','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 502 - Clerical','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 503 - Acct','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 504 - HR','5' UNION ALL
SELECT 'Fighter','4' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 505 - Ammunition','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 506 - Stuff','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 507 - More Stuff','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 508 - Cat5 Cables','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 509 - Mops','5' UNION ALL
SELECT 'Bombers','4' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 510 - Wings','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 511 - HUD','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 512 - Radar','5' UNION ALL
SELECT 'Facility 1 - LM Cost Center: 513 - Blackberry Phones','5' UNION ALL
SELECT 'Deep 6 - Norfolk, VA','2' UNION ALL
SELECT 'LM - Submarines','3' UNION ALL
SELECT 'Virginia Class Subs','4' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 514 - Water','5' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 515- Reactors','5' UNION ALL
SELECT 'Ohio Class Subs','4' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 516 - Torpedos','5' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 517 - Counter Measures','5' UNION ALL
SELECT 'Facility 2 - LM Cost Center: 518 - Bang and Olufsen party speakers','5'
GO
;WITH FindGroups AS (
SELECT
td.RowOrder,
td.Entity,
td.IndentLevel,
MAX(CASE WHEN td.IndentLevel = 1 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L1,
MAX(CASE WHEN td.IndentLevel = 2 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L2,
MAX(CASE WHEN td.IndentLevel = 3 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L3,
MAX(CASE WHEN td.IndentLevel = 4 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L4
FROM
#Test_Data td
)
SELECT
fg1.Entity AS [System],
fg2.Entity AS Facility,
fg3.Entity AS ServiceLine,
fg4.Entity AS Division,
fg5.Entity AS Department
FROM
FindGroups fg1
LEFT JOIN FindGroups fg2
ON fg1.L1 = fg2.L1
AND fg2.IndentLevel = 2
LEFT JOIN FindGroups fg3
ON fg2.L2 = fg3.L2
AND fg3.IndentLevel = 3
LEFT JOIN FindGroups fg4
ON fg3.L3 = fg4.L3
AND fg4.IndentLevel = 4
LEFT JOIN FindGroups fg5
ON fg4.L4 = fg5.L4
AND fg5.IndentLevel = 5
WHERE
fg1.IndentLevel = 1
The Results...
SystemFacilityServiceLineDivisionDepartment
----------------------------------------------------------------------------------------------------------------------------------------------
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 500 - Admin
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 501 - Tech
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 502 - Clerical
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 503 - Acct
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 504 - HR
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 505 - Ammunition
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 506 - Stuff
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 507 - More Stuff
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 508 - Cat5 Cables
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 509 - Mops
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 510 - Wings
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 511 - HUD
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 512 - Radar
Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 513 - Blackberry Phones
Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesVirginia Class SubsFacility 2 - LM Cost Center: 514 - Water
Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesVirginia Class SubsFacility 2 - LM Cost Center: 515- Reactors
Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesOhio Class SubsFacility 2 - LM Cost Center: 516 - Torpedos
Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesOhio Class SubsFacility 2 - LM Cost Center: 517 - Counter Measures
Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesOhio Class SubsFacility 2 - LM Cost Center: 518 - Bang and Olufsen party speakers
Also... Nice work on setting up the OP! Having DDL & test data makes it much easier to focus on solutions. Thank you for that. 😀
June 16, 2015 at 1:34 pm
Jason! This worked perfectly! Thanks so much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply