I resolved it with the following code.

SELECT 
    mg.[name] AS group_name,
    NULL AS subgroup_name,
    cmc.country_id
FROM dbo.starui_country_mastergroup mg
INNER JOIN...

I've come up with this T-SQL but it doesn't show the master group country id only the sub group country id. I need both in the country id column.

            
May 5, 2025 at 5:55 am
            
#4587787

            
            
                                                
        

Thanks for the reply.

Yeah the 6th table stores the codes and isn't need for this task. Unofrtunaely I didn't create this just inherited it, I have already my concerns with...

Thanks for the reply, I have resolved the problem with a self referencing LEFT JOIN on the non measure columns.

Thanks Anthony, I knew it was something stupid.

INSERT [dbo].[Hier_table] ([Dimensions], [Management], [Management_Name], [Level]) VALUES (N'OBU1', N'GPFX', N'XXX Elephant Total', 2)
GO
INSERT [dbo].[Hier_table] ([Dimensions], [Management], [Management_Name], [Level]) VALUES (N'GPFX', N'GPS1', N'XXX XXX Fire Total', 3)
GO
INSERT [dbo].[Hier_table]...

I have attached a small sample expected output as CSV and a screen shot. Level 2 & 3 are only one row but the other rows can be one or...

• This reply was modified 5 years, 6 months ago by ringovski.

I came up with the following self referencing CTE but its not moving along the levels, so IDK.

With CTE
  AS(
SELECT Dimensions,Management,[Description], [Level]
FROM [dbo].[Hier_table]
WHERE Dimensions = 'OBU1'
UNION...

I am not trying to 'game the system' I want to improve my understanding of cursor's. Any company can ask many different questions and will also change there questions. I...

Thanks for the reply. 


Why are you minus id from row number?
[p].[id] - ROW_NUMBER() OVER (PARTITION BY [p].[product_name] ORDER BY [p].[id], [p].[start_date])


I don't have access to the tables as it was a test this morning and I wanted to work it out. You have put the max inside the inner query...

The business can't give me a clear answer on what they are doing. Old spreadsheets they have using for so many years and no one knows 🙂 Thanks for all...

mmm good point, didn't think of that not sure I will find out tomorrow.

Thanks a lot for the script. Can you explain the logic behind it so i can understand it?

Thanks for the reply, but how do i apply your cte's to my statement?