Geir Forsmo
Reputation: 149
Trying to create a parent-child hierarchy in sql server
I have a data set that is sorted by account key. when coming to certain rows which have NULL as group key, it should add current account key as a parent key for all above rows which have NULL as parentkey. So when coming to the next row with null as group key, you would set the current account key as parent key for all rows above that have parent key like null.
I have tried to copy a dataset below as mark down table but as you see I can't say I succeeded very well, but I hope some of you can help with the t-sql syntax to create a parent-child hierarchy of this
| AccountKey | ParentKey | GroupKey | AccountNumber | Cat | LineName | LineId |
|------------|-----------|----------|---------------|----------|------------------------------|--------------------------------------|
| 1 | NULL | 7 | 3040 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 2 | NULL | 7 | 3041 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 3 | NULL | 7 | 3081 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 4 | NULL | 7 | 3082 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 5 | NULL | 7 | 3083 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 6 | NULL | 7 | 3085 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 7 | NULL | 7 | 3086 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 8 | NULL | 7 | 3087 | Account | Salg fisk | C6BCDFB2-1AAC-4D05-94F1-879CDC615D76 |
| 9 | NULL | 2 | 3000 | Account | Salg annet | 26AC86B2-0667-463E-B994-11A5C6D519A6 |
| 10 | NULL | 2 | 3010 | Account | Salg annet | 26AC86B2-0667-463E-B994-11A5C6D519A6 |
| 11 | NULL | 2 | 3020 | Account | Salg annet | 26AC86B2-0667-463E-B994-11A5C6D519A6 |
| 12 | NULL | 2 | 3030 | Account | Salg annet | 26AC86B2-0667-463E-B994-11A5C6D519A6 |
| 41 | NULL | 11 | 3050 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 42 | NULL | 11 | 3600 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 43 | NULL | 11 | 3601 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 44 | NULL | 11 | 3610 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 45 | NULL | 11 | 3615 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 46 | NULL | 11 | 3690 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 47 | NULL | 11 | 3691 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 48 | NULL | 11 | 3701 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 49 | NULL | 11 | 3705 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 50 | NULL | 11 | 3720 | Account | Andre driftsinntekter | 65FFB620-AE42-4BE5-A6E7-BF3339AA04DF |
| 67 | NULL | NULL | NULL | SubTotal | Sum inntekter | NULL |
| 68 | NULL | 13 | 4120 | Account | Innkjøp smolt/settefisk/rogn | F9EE1CE4-22C7-400B-BC9D-E2D3214A5113 |
| 69 | NULL | 10 | 4010 | Account | Vareforbruk fôr | 04E63B6D-CA54-423D-8A44-A4ED99861975 |
| 70 | NULL | 10 | 4901 | Account | Vareforbruk fôr | 04E63B6D-CA54-423D-8A44-A4ED99861975 |
| 71 | NULL | 3 | 4000 | Account | Andre varekostnader | DB7FABAB-7ABA-4B9A-9720-1B538D99B3C8 |
| 72 | NULL | 3 | 4020 | Account | Andre varekostnader | DB7FABAB-7ABA-4B9A-9720-1B538D99B3C8 |
| 73 | NULL | 3 | 4030 | Account | Andre varekostnader | DB7FABAB-7ABA-4B9A-9720-1B538D99B3C8 |
| 133 | NULL | 8 | 4925 | Account | Beholdningsendring fisk | A8BA6F19-A792-44A1-AA21-8F79DB24D224 |
| 134 | NULL | NULL | NULL | SubTotal | Sum varekostnader | NULL |
| 135 | NULL | 12 | 5000 | Account | Lønn og sosiale kostnader | 5C475EDE-3731-4D39-B11A-C8EE72213FF6 |
| 136 | NULL | 12 | 5001 | Account | Lønn og sosiale kostnader | 5C475EDE-3731-4D39-B11A-C8EE72213FF6 |
| 137 | NULL | 12 | 5005 | Account | Lønn og sosiale kostnader | 5C475EDE-3731-4D39-B11A-C8EE72213FF6 |
| 138 | NULL | 12 | 5009 | Account | Lønn og sosiale kostnader | 5C475EDE-3731-4D39-B11A-C8EE72213FF6 |
| 263 | NULL | NULL | NULL | SubTotal | Sum lønnskostnadern | NULL |
| 462 | NULL | NULL | NULL | SubTotal | RESULTAT ETTER SKATT | NULL
Upvotes: 0
Views: 97
Answers (2)
etsa
Reputation: 5060
If I understood your question you could use:
SELECT Accountkey, ParentKey,GroupKey,AccountNumber, NEW_PARID
FROM (
SELECT Accountkey, ParentKey,GroupKey,AccountNumber, AccountKey AS NEW_PARID, LAG(ACCOUNTKEY) OVER (ORDER BY Accountkey) AS PREC
FROM MYT
WHERE GroupKey IS NULL
UNION ALL
SELECT A.Accountkey, A.ParentKey,A.GroupKey,A.AccountNumber, B.Accountkey AS NEW_PARID, B.PREC
FROM MYT A
INNER JOIN ( SELECT Accountkey, ParentKey,GroupKey,AccountNumber, AccountKey AS NEW_PARID, LAG(ACCOUNTKEY) OVER (ORDER BY Accountkey) AS PREC
FROM MYT
WHERE GroupKey IS NULL) B ON A.Accountkey < B.Accountkey AND (B.PREC IS NULL OR B.PREC<A.accountKey)
WHERE A.GroupKey IS NOT NULL
AND B.GroupKey IS NULL
) X ORDER BY ACCOUNTKEY
You can write it in this way too (it's the same query):
WITH X AS (SELECT Accountkey, ParentKey,GroupKey,AccountNumber, AccountKey AS NEW_PARID, LAG(ACCOUNTKEY) OVER (ORDER BY Accountkey) AS PREC
FROM MYT
WHERE GroupKey IS NULL)
SELECT X.*
FROM X
UNION ALL
SELECT A.Accountkey, A.ParentKey,A.GroupKey,A.AccountNumber, X.Accountkey AS NEW_PARID, X.PREC
FROM MYT A
INNER JOIN X ON A.Accountkey < X.Accountkey AND (X.PREC IS NULL OR X.PREC<A.accountKey)
WHERE A.GroupKey IS NOT NULL
Output (MYT is the name of the table, the new parentid column is NEW_PARID):
+------------+-----------+----------+---------------+-----------+
| Accountkey | ParentKey | GroupKey | AccountNumber | NEW_PARID |
+------------+-----------+----------+---------------+-----------+
| 1 | NULL | 7 | 3040 | 67 |
| 2 | NULL | 7 | 3041 | 67 |
| 3 | NULL | 7 | 3081 | 67 |
| 4 | NULL | 7 | 3082 | 67 |
| 5 | NULL | 7 | 3083 | 67 |
| 6 | NULL | 7 | 3085 | 67 |
| 7 | NULL | 7 | 3086 | 67 |
| 8 | NULL | 7 | 3087 | 67 |
| 9 | NULL | 2 | 3000 | 67 |
| 10 | NULL | 2 | 3010 | 67 |
| 11 | NULL | 2 | 3020 | 67 |
| 12 | NULL | 2 | 3030 | 67 |
| 41 | NULL | 11 | 3050 | 67 |
| 42 | NULL | 11 | 3600 | 67 |
| 43 | NULL | 11 | 3601 | 67 |
| 44 | NULL | 11 | 3610 | 67 |
| 45 | NULL | 11 | 3615 | 67 |
| 46 | NULL | 11 | 3690 | 67 |
| 47 | NULL | 11 | 3691 | 67 |
| 48 | NULL | 11 | 3701 | 67 |
| 49 | NULL | 11 | 3705 | 67 |
| 50 | NULL | 11 | 3720 | 67 |
| 67 | NULL | NULL | NULL | 67 |
| 68 | NULL | 13 | 4120 | 134 |
| 69 | NULL | 10 | 4010 | 134 |
| 70 | NULL | 10 | 4901 | 134 |
| 71 | NULL | 3 | 4000 | 134 |
| 72 | NULL | 3 | 4020 | 134 |
| 73 | NULL | 3 | 4030 | 134 |
| 133 | NULL | 8 | 4925 | 134 |
| 134 | NULL | NULL | NULL | 134 |
| 135 | NULL | 12 | 5000 | 263 |
| 136 | NULL | 12 | 5001 | 263 |
| 137 | NULL | 12 | 5005 | 263 |
| 138 | NULL | 12 | 5009 | 263 |
| 263 | NULL | NULL | NULL | 263 |
| 462 | NULL | NULL | NULL | 462 |
+------------+-----------+----------+---------------+-----------+
Updated 20171221 - for MSSQL 2008 You can try this (but pay attention for performance if you have a large dataset):
SELECT A.ACCOUNTKEY
, A.PARENTKEY
, (SELECT MIN(B.ACCOUNTKEY) FROM MYT B WHERE B.GROUPKEY IS NULL AND A.ACCOUNTKEY<=B.ACCOUNTKEY) AS NEW_PARID
FROM MYT A
/* WHERE A.GROUPKEY IS NOT NULL*/
Upvotes: 2
Geir Forsmo
Reputation: 149
This is the code I need to rewrite to work on SQL Server 2008
SELECT AccountKey,
LineName,
AccountName,
GroupKey,
AccountNumber,
ParentAccountKey
INTO tempAccount
FROM
(
SELECT AccountKey,
LineName,
AccountName,
GroupKey,
AccountNumber,
AccountKey AS ParentAccountKey,
LAG(AccountKey) OVER(ORDER BY AccountKey) AS PREC
FROM tempTable2
WHERE GroupKey IS NULL
UNION ALL
SELECT A.AccountKey,
A.LineName,
A.AccountName,
A.GroupKey,
A.AccountNumber,
B.AccountKey AS ParentAccountKey,
B.PREC
FROM tempTable2 A
INNER JOIN
(
SELECT AccountKey,
LineName,
AccountName,
GroupKey,
AccountNumber,
AccountKey AS ParentAccountKey,
LAG(AccountKey) OVER(ORDER BY AccountKey) AS PREC
FROM tempTable2
WHERE GroupKey IS NULL
) B ON A.AccountKey < B.AccountKey
AND (B.PREC IS NULL
OR B.PREC < A.AccountKey)
WHERE A.GroupKey IS NOT NULL
AND B.GroupKey IS NULL
) X
ORDER BY AccountKey;
Upvotes: 0
Related Questions
- T-SQL creating a hierarchy out of orderly numbers
- Build hierarchy table from main nodes and level nodes
- How to get a hierarchy table in Sql Server
- How can I create multi level hierarchy in SQL Server
- sql-server detect parents in parent child hierarchy
- Creating data hierarchy in SQL Server
- Hierarchies with multiple parents in SQL Server
- sql Traversing parent child
- Implementing Hierarchy in SQL
- Creating a hierarchy in SQL Server