How to duplicate row in SQL Query result....

Question

SELECT * FROM TABLE1

ID      Name 
1       SOF
2       USER

I want query which can one or two or four .... times ID = 1 in row like

What will be the Query of this in Ms.Access?

ID      Name 
1       SOF
1       SOF
1       SOF
2       USER

Answer 1

Following example uses SQL Server's CTE syntax.

As it stands, the @ID gets selected @Count times, all other records are selected one time. It would be fairly easy to extend it to whatever count for whatever ID's you like.

Setup

DECLARE @Table TABLE (ID INTEGER, Name VARCHAR(32))
DECLARE @Count INTEGER
DECLARE @ID INTEGER

SET @Count = 3
SET @ID = 1

INSERT    INTO @Table 
          SELECT 1, 'SOF'
UNION ALL SELECT 2, 'USER'

SQL Statement

;WITH Multiple AS (
  SELECT  ID, Name, cnt = 1
  FROM    @Table
  WHERE   ID = @ID
  UNION ALL
  SELECT  ID, Name, cnt = cnt + 1
  FROM    multiple
  WHERE   cnt < @Count
)
SELECT  ID, Name
FROM    Multiple
UNION ALL
SELECT  ID, Name
FROM    @Table
WHERE   ID <> @ID

Answer 2

This solution depends on having a LabelCount field (column) with the required number of labels, and a Coiunter table, with a field, Num, that contains integers from 1 to maximum number of labels required:

SELECT Id, [Name]
FROM Table1, [Counter]
WHERE Counter.Num<=[LabelCount]

Answer 3

In an Access report it can be done without changing the SQL. This really ancient Knowledge Base article explains it, and I've had it in production use for nearly 15 years!

How to Skip Used Mailing Labels and Print Duplicates