Hi Can someone help me out on a query to be used in reporting service. I need to print X number of labels for each returned result in a SQL statement. I have a SQL statemement that has the following result [School], [Address], [NumberOfLabels] First, Street1, 1 Second, Street2, 3 Third, Street3, 5 The [NumberOfLabels] is from a lot of different parameters. What I need is [School], [Address] First, Street1 Second, Street2 Second, Street2 Second, Street2 Third, Street3 Third, Street3 Third, Street3 Third, Street3 Third, Street3 Now since the statement should be used for reporting service, and the database is locked ( MSCRM Onpremise ) for creating procedures or tables i need it all in just one complete statement I was thinking something along the following, but I can't get it to work. I know the last part of the statement is completely wrong, but I want to show my idea. SELECT [School], [Address], Count([School]) + 1 AS [NumberOfLabels] from [Mytable] Group By [School] CROSS JOIN (SELECT TOP(Count([School]) + 1 AS [NumberOfLabels] from [Mytable] Group By [School] WHERE [MyTable].[School] = [NewTable].[School]) as [NewTable] Thank you in advance Morten Kessel

Hi Morten, We cannot get the result directly by select statement. However, we can achieve this by insert row base the column value. Do you have the permission to run a script in the database, if you have, then we can run a script in the dataset to insert records base on the value NumberOfLabels. I have tested it on my environment. create table RepeatNum ( ID int, Name varchar(10) ) insert into RepeatNum values (1,'a'); insert into RepeatNum values (2,'b'); insert into RepeatNum values (3,'c'); select * from RepeatNum declare RepeatRows cursor for select ID,Name from RepeatNum; declare @tempID int; declare @ID int; declare @tempName Varchar(10); declare @rows int; select @rows=COUNT(distinct ID) from RepeatNum; open RepeatRows; fetch next from RepeatRows into @tempID, @tempName WHILE @@FETCH_STATUS = 0 begin set @ID = @tempID; while(@ID-1>0) begin insert into RepeatNum values(@tempID,@tempName); set @ID = @ID-1; end set @rows = @rows -1; if @rows=0 return fetch next from RepeatRows into @tempID, @tempName End select * from RepeatNum order by ID If this is not you want, I am afraid there is no other approach to achieve this. Thank you for you understanding. Regards, Charlie Liao If you have any feedback on our support, please click here.Charlie Liao TechNet Community Support

Thank you for your answer Charlie, I'm not sure if I have the nessesary permission to use your solution in a report. MSCRM is very strict about adding script to the database since it could cause problems when upgrading. I have found a way to solve the issue myself, that is better suited for reports. ;with C as ( select [School], [Address], [NumberOfLabels] - 1 as [Labels] from @Source union all select [School], [Address], [NumberOfLabels] - 1 as [Labels] from C where [Labels] > 0 ) select [School], [Address] from C