问题描述:

Let us say I have some data I would like to repeat N times. A naive approach would be this:

 IF OBJECT_ID('dbo.Data', 'U') IS NOT NULL

DROP TABLE dbo.Data

CREATE TABLE Data

(

DataId INT NOT NULL PRIMARY KEY,

DataValue NVARCHAR(MAX) NOT NULL

)

INSERT INTO Data (DataId, DataValue)

SELECT 1, 'Value1' UNION ALL

SELECT 2, 'Value2' UNION ALL

SELECT 3, 'Value3' UNION ALL

SELECT 4, 'Value4' UNION ALL

SELECT 5, 'Value5'

DECLARE @RowsRequired INT

DECLARE @Counter INT

DECLARE @NumberOfRows INT

SET @RowsRequired = 22

IF OBJECT_ID('tempdb..#TempData') IS NOT NULL DROP TABLE #TempData

CREATE TABLE #TempData

(

Id INT IDENTITY(1,1),

DataValue NVARCHAR(MAX)

)

SELECT @NumberOfRows = COUNT(*) FROM Data

SET @Counter = 1

WHILE @RowsRequired > 0

BEGIN

INSERT INTO #TempData

SELECT DataValue FROM Data WHERE DataId = @Counter

SET @Counter = @Counter + 1

SET @RowsRequired = @RowsRequired - 1

IF(@Counter > @NumberOfRows)

BEGIN

SET @Counter = 1

END

END

SELECT * FROM #TempData

Here @RowsRequired determines how many rows are required. Could this be rephrased in a set based form? Thanks.

Here is a SQLFiddle with the code.

网友答案:

Try this instead:

DECLARE @RowsRequired INT = 22

;WITH CTE AS
(
  SELECT DataId, DataValue, ROW_NUMBER() over (PARTITION BY DataId ORDER BY DataId) sort
  FROM DATA
  CROSS JOIN
  (
    SELECT TOP (@RowsRequired) 0 d
    FROM master..spt_values
  ) d
)
SELECT TOP (@RowsRequired) ROW_NUMBER() over (order by sort),  DataValue
FROM CTE
ORDER BY sort, 1
网友答案:

I tried this and worked for me.

declare @requiredrows int

set @requiredrows = 22;

declare @foreachrow int
select @foreachrow = @requiredrows / Count(*) from Data;

select top (@requiredrows) * from
(
select *, ROW_NUMBER() over(partition by dataId order by number) rno
from Data
Cross Join master..spt_values
) A
where rno <= @foreachrow + 1

Hope it will help.

相关阅读:
Top