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 4 years, 9 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?

Thanks for the replies.
I got it working with  a excel macro. I recorded the macro in excel doing what I wanted this using a .BAT and .VBS script and...

Thanks for the reply.
I have created a excel macro which works when I run it in excel. I am trying to run it via the script task. But It comes...