CODEWITHSUNDEEP
sqlsql-server
Bob Probst
Bob Probst

Reputation: 9641

How to return N records in a SELECT statement without a table

I'm creating a NACHA file and if the number of records in the file is not a multiple of 10, we need to insert enough "dummy" records filled with nines (replicate('9',94)) to hit that next tens place.

I know that I could write a loop or perhaps fill a temp table with 10 records full of nines and select the top N. But those options feel clunky.

I was trying to think of a single select statement that could do it for me. Any ideas?

select nacha_rows
from NACHA_TABLE
union all
select replicate('9',94)  --do this 0 to 9 times

Upvotes: 4

Views: 296

Answers (5)

Serpiton
Serpiton

Reputation: 3684

One idea is to prepare 9 filler rows than append only the ones needed to reach the next tens, same idea of JChao, with a different implementation

With Filler AS (
  SELECT n.n, replicate('9',94) nacha_rows
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(n)
)
SELECT nacha_rows
FROM   NACHA_TABLE
UNION ALL
SELECT nacha_rows
FROM   Filler
       OUTER APPLY (SELECT count(1) % 10 last
                    FROM   NACHA_TABLE) l 
WHERE  filler.n + l.last <= 10
  AND  l.last > 0 -- to prevent filler line when NACHA_TABLE has exactly 10x rows

SQLFiddle demo

Upvotes: 1

Anon
Anon

Reputation: 10908

The formula (10-COUNT(*)%10)%10 tells you how many rows to add, so you can just select that many dummy rows from an existing dummy table.

SELECT nacha_rows
FROM NACHA_TABLE
UNION ALL
SELECT TOP (SELECT (10-COUNT(*)%10)%10 FROM NACHA_TABLE) REPLICATE('9',94)
FROM master.dbo.spt_values

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

This should work. Created a temp table with 9 rows of the dummy data. Then used modulo to determine how many extra rows should be returned. Then return the full dataset. If you wanted to make it pretty you could take the modulo piece out and calculate it one time in a variable. 

;WITH dummydata (num, nines) 
 AS (SELECT 1 AS num, Replicate('9', 94) 
     UNION ALL 
     SELECT num + 1, Replicate('9', 94) 
     FROM   dummydata 
     WHERE  num < 9) 

SELECT * 
FROM   nacha_table 
UNION ALL 
SELECT nines 
FROM   dummydata 
WHERE  num >= CASE 
            WHEN (SELECT Count(1) % 10 FROM nacha_table) = 0 THEN 10 
            ELSE (SELECT Count(1) % 10 FROM nacha_table) 
          END

Upvotes: 1

Alex K.
Alex K.

Reputation: 175766

A way using a set of 10 rows and joining;

;with T(ord) as
(
    select 1 as ord union all select ord + 1 from T where ord < 10
)
select isnull(nacha_rows, replicate('9', 94)) from T left join (
    select  
        ROW_NUMBER() over (order by nacha_rows) row, nacha_rows
    from NACHA_TABLE
) T2 on row = ord

Edit; Just realised that of course the table could have > 10 rows in the first place in which case this wont work.

Upvotes: 0

Believe2014
Believe2014

Reputation: 3964

Looks like you need a dummy select statement:

select '1' as [col1], 'abcdef' as [col 2]
union all 
select '2' as [col1], 'abcdef' as [col 2]
union all 
select '3' as [col1], 'abcdef' as [col 2]
union all 
select '4' as [col1], 'abcdef' as [col 2]

Upvotes: 0

Related Questions