Populate the number ranges & concatenate with text

问题:

Wondering how to build a logic for the below scenario (I am using SQL Server 2008 & above).

In TblMaster each record will have a Start value and an End value. Along with that a CharToAdd varchar column as well. If the range says 1 to 3 then in the TblDetails there should be an entry for 1,2,3. Not sure whether I am explaining it effectively so have provided a screenshot with sample input/output as well.

Ranges would be anywhere between 5000 to 100000 (max) so performance is a concern as well.

Table Schema:

CREATE TABLE [dbo].[VMaster](
    [VID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [VName] [varchar](30) NOT NULL
)
GO

CREATE TABLE [dbo].[TblMaster](
    [SID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [VID] [int] NOT NULL,
    [CreatedDate] [datetime] default (getdate()) NOT NULL,
    [CharToAdd] [varchar](10) NOT NULL,
    [Start] [int]  NOT NULL,
    [End] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblDetails](
    [DetailsID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [SID] [int] NOT NULL,
    [Sno] [int] NOT NULL,
    [ConcatenatedText] [varchar](20) NOT NULL,
    [isIssued] [bit] default (0) NOT NULL,
    [isUsed] [bit] default (0) NOT NULL
)
GO

ALTER TABLE [dbo].[TblMaster]  WITH CHECK ADD CONSTRAINT [fk_SI_id] FOREIGN KEY([VID])
REFERENCES [dbo].[VMaster] ([VID])
GO

ALTER TABLE [dbo].[TblMaster] CHECK CONSTRAINT [fk_SI_id]
GO

Sample data for the tables:

Insert into dbo.VMaster Values ('A1')
GO
Insert into dbo.TblMaster Values (1,default, 'ABC', 100, 105)
GO

Sample records inserted into the tables and the output expected in TblDetails:

EDIT: What if the record in TblMaster is getting updated later? If both the bit fields in TblDetails are 0 for all the corresponding auto-generated rows then should delete the old records and regenerate based on the "new range" updated.

--Modifying the original range. So the records 100, 104, 105 has to be removed 
-- from tblDetails if its isIssued, isUsed are both 0. 
-- If not will have to reject this update 
Update dbo.TblMaster 
Set Start = 101, [End] = 103
Where SID = 1

--Another scenario. We need to remove 100,101,102,103,104,105 from tblDetails if
-- its isIssued, isUsed are both 0. Then create entries from 1000 to 1500 in tblDetails. 
-- If isIssued, isUsed are not 0 then will have to reject this update 
Update dbo.TblMaster 
Set Start = 1000, [End] = 1500
Where SID = 1

回答1:

I use a UDF to create Dynamic Ranges. A Numbers or Tally Table would do the same

Declare @Table table (SID int,VID int,CreateDate DateTime,CharToAdd varchar(25),Start int, [End] Int)
Insert Into @Table values
(1,1,'2016-06-30 19:56:14.560','ABC',100,105),
(2,2,'2016-06-30 19:56:14.560','ABC',10,15)

Declare @Min int,@Max int
Select @Min=min(Start),@Max=max([End]) From @Table

Select B.SID
      ,Sno = A.RetVal
      ,ConcetratedText = concat(B.CharToAdd,A.RetVal)
 From (Select RetVal=Cast(RetVal as int) from [dbo].[udf-Create-Range-Number](@Min,@Max,1)) A
 Join @Table B on A.RetVal Between B.Start and B.[End]
 Order By B.Sid,A.RetVal

Returns

SID Sno ConcetratedText
1   100 ABC100
1   101 ABC101
1   102 ABC102
1   103 ABC103
1   104 ABC104
1   105 ABC105
2   10  ABC10
2   11  ABC11
2   12  ABC12
2   13  ABC13
2   14  ABC14
2   15  ABC15

The UDF if you care to use it (you may have to set maxrecursion to 0)

CREATE FUNCTION [dbo].[udf-Create-Range-Number] (@R1 money,@R2 money,@Incr money)

-- Syntax Select * from [dbo].[udf-Create-Range-Number](0,100,2)

Returns 
@ReturnVal Table (RetVal money)

As
Begin
    With NumbTable as (
        Select NumbFrom = @R1
        union all
        Select nf.NumbFrom + @Incr
        From NumbTable nf
        Where nf.NumbFrom < @R2
    )
    Insert into @ReturnVal(RetVal)

    Select NumbFrom from NumbTable Option (maxrecursion 32767)

    Return
End

回答2:

Go recursive :

    ;with recur as (select vid, chartoadd, [start] as nm, [end], 1 as Detailsid  from tblMaster
                   union all
                   select vid, chartoadd, nm+1, [end], Detailsid+1  from recur where nm<[end])
    select Detailsid, vid, chartoadd+cast(nm as char) as ConcatenatedText
      from recur
OPTION (MAXRECURSION 0)

OUPUT

Detailsid   vid ConcatenatedText
1   1   ABC100                           
2   1   ABC101                           
3   1   ABC102                           
4   1   ABC103                           
5   1   ABC104                           
6   1   ABC105 

回答3:

You can certainly use one of many tricks for creating a list of numbers. What's probably easiest for you is to actually create one in the database. I'll leave that task up to you. So let's assume you have a Numbers table with values from 0 up to the maximum possible rows in a range.

select ...
from
    Master m
    inner join Numbers n
        on n.Number between 0 and m.End - m.Start
    inner join Details d
        d.Sno = n.Number + m.Start /* and d.SID = m.SID ? */

回答4:

The following example illustrates how it is dynamic

Notice the UPDATE

Declare @Table table (SID int,VID int,CreateDate DateTime,CharToAdd varchar(25),Start int, [End] Int)
Insert Into @Table values
(1,1,'2016-06-30 19:56:14.560','ABC',1000,1500),
(2,2,'2016-06-30 19:56:14.560','ABC',10,15)

-----------------------------------------------------------
Update @Table Set Start=101,[End]=103 where SID=1
----------------------------------------------------------

Declare @Min int,@Max int
Select @Min=min(Start),@Max=max([End]) From @Table


Select B.SID
      ,Sno = A.RetVal
      ,ConcetratedText = concat(B.CharToAdd,A.RetVal)
 From (Select RetVal=Cast(RetVal as int) from [dbo].[udf-Create-Range-Number](@Min,@Max,1)) A
 Join @Table B on A.RetVal Between B.Start and B.[End]
 Order By B.Sid,A.RetVal

Returns

SID Sno ConcetratedText
1   101 ABC101            --<< 101 - 103 not 1000 - 1500
1   102 ABC102            --<< 101 - 103 not 1000 - 1500
1   103 ABC103            --<< 101 - 103 not 1000 - 1500
2   10  ABC10
2   11  ABC11
2   12  ABC12
2   13  ABC13
2   14  ABC14
2   15  ABC15