SQL Server: calculate sequence in SQL query

Question

I have the following four tables in SQL Server 2008R2:

DECLARE @ParentGroup TABLE (ParentGroup_ID INT, ParentGroup_Name VARCHAR(100)); DECLARE @ChildGroup TABLE (ChildGroup_id INT, ChildGroup_name VARCHAR(100), ParentGroup_id INT); DECLARE @Entity TABLE ([Entity_id] INT, [Entity_name] VARCHAR(100)); DECLARE @ChildGroupEntity TABLE (ChildGroupEntity_id INT, ChildGroup_id INT, [Entity_ID] INT); INSERT INTO @parentGroup VALUES (1, 'England'), (2, 'USA'); INSERT INTO @ChildGroup VALUES (10, 'Sussex', 1), (11, 'Essex', 1), (12, 'Middlesex', 1); INSERT INTO @entity VALUES (100, 'Entity0'),(101, 'Entity1'),(102, 'Entity2'),(103, 'Entity3'),(104, 'Entity4'),(105, 'Entity5'),(106, 'Entity6'); INSERT INTO @ChildGroupEntity VALUES (1000, 10, 100), (1001, 10, 101), (1002, 10, 102), (1003, 11, 103), (1004, 11, 104), (1005, 12, 100), (1006, 12, 105), (1007, 12, 106); /* SELECT * FROM @parentGroup SELECT * FROM @ChildGroup SELECT * FROm @entity SELECT * FROM @ChildGroupEntity */ 

The relationships between the tables below:

 SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 0 [ChildGroupSequence], 0 [EntitySequence] FROM @ChildGroupEntity cge INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID] ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name] 

The output of the above query:

 ------------------------------------------------------------------------------- ParentGroup_Name|ChildGroup_name|Entity_name|ChildGroupSequence|EntitySequence| ------------------------------------------------------------------------------- England |Essex |Entity3 |0 |0 | England |Essex |Entity4 |0 |0 | England |Middlesex |Entity0 |0 |0 | England |Middlesex |Entity5 |0 |0 | England |Middlesex |Entity6 |0 |0 | England |Sussex |Entity0 |0 |0 | England |Sussex |Entity1 |0 |0 | England |Sussex |Entity2 |0 |0 | ------------------------------------------------------------------------------- 

Now I want to know the child groups and all entities associated with the child groups for parent group 1. In addition, I want to calculate [ChildGroupSequence], [EntitySequence], as well as for the following logic:

• The column ChildGroupSequence should represent the sequence of child groups in the parent group, starting from 1000 and increasing by 100. That is, the first subgroup will be 1000, the second subgroup will be 1100.
• The EntitySequence column should represent a sequence of entities in a child group, starting at 100 and incrementing by one, resetting for each subgroup. That is, the first object in child group 1 starts at 100, like the first object in child group 2.

So, the output should be in the following format:

 ------------------------------------------------------------------------------- ParentGroup_Name|ChildGroup_name|Entity_name|ChildGroupSequence|EntitySequence| ------------------------------------------------------------------------------- England |Essex |Entity3 |1000 |100 | England |Essex |Entity4 |1000 |101 | England |Middlesex |Entity0 |1100 |100 | England |Middlesex |Entity5 |1100 |101 | England |Middlesex |Entity6 |1100 |102 | England |Sussex |Entity0 |1200 |100 | England |Sussex |Entity1 |1200 |101 | England |Sussex |Entity2 |1200 |102 | ------------------------------------------------------------------------------- 

I can do this easily by reading the values ​​at the application level (.Net program), but I want to learn the SQL server by experimenting with a few such things. Can someone help me write this SQL query?

Any help would be greatly appreciated. Thanks in advance.

EDIT: My sample data does not seem to correctly reflect the first rule, the rule states that ChildGroupSequence should be increased by 100, and the sample number of steps should be increased by 1. The second query reflects the increase by 100. @jpw: Thank you very much for pointing this out.

Answer 1

I believe that this can be done using the separation and rating performs the following functions:

 SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 999 + DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) AS [ChildGroupSequence], 99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY ChildGroup_name, Entity_name) AS [EntitySequence] FROM @ChildGroupEntity cge INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID] ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name] 

This query generates the example you deduced. Your sample data does not seem to correctly reflect the first rule, although, since the rule states that ChildGroupSequence should be increased by 100, and the sample fetch is increased by 1. The second query reflects an increment of 100:

 SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 900 + 100 * DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) AS [ChildGroupSequence], 99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY ChildGroup_name, Entity_name) AS [EntitySequence] FROM @ChildGroupEntity cge INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID] ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name] 

Please see the SQL Fiddle sample for examples of both queries.

Perhaps the request should be split by identifier, and not by name, if Sussex arrives before Essex, because it has a lower identifier, and the request will be as follows:

 SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 900 + 100 * DENSE_RANK() OVER(PARTITION BY pg.ParentGroup_ID ORDER BY cg.ChildGroup_ID) AS [ChildGroupSequence], 99 + ROW_NUMBER() OVER(PARTITION BY pg.ParentGroup_ID, cg.ChildGroup_ID ORDER BY cg.ChildGroup_ID, cge.Entity_ID) AS [EntitySequence] FROM @ChildGroupEntity cge INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID] ORDER BY pg.ParentGroup_ID, cg.ChildGroup_ID, [Entity_name] 

Answer 2

 SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], LU.R [ChildGroupSequence], 99 + ROW_NUMBER() OVER (PARTITION BY LU.ParentGroup_id,LU.ChildGroup_id ORDER BY ChildGroup_name) [EntitySequence] FROM @ChildGroupEntity cge JOIN ( SELECT cg.ChildGroup_id, cg.ChildGroup_name, pg.ParentGroup_id, pg.ParentGroup_Name, 999 + (ROW_NUMBER() OVER (ORDER BY cg.ChildGroup_id)) [R] FROM @ChildGroup cg JOIN @parentGroup pg On pg.ParentGroup_ID = cg.ParentGroup_ID) LU ON cge.ChildGroup_id = LU.ChildGroup_id INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID] ORDER BY LU.ParentGroup_Name, LU.ChildGroup_name, e.[Entity_name] 

Result:

Answer 3

You can solve this problem using ranking functions.

 SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 899 + DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) + 100 * DENSE_RANK() OVER(ORDER BY ParentGroup_Name ASC) AS ChildGroupSequence, 99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY [Entity_name]) AS [EntitySequence] FROM @ChildGroupEntity cge INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID] ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name] 

The result can be found here. SQL Fiddle