CODEWITHSUNDEEP
sqlsql-server
Carlos80
Carlos80

Reputation: 433

SQL Duplicate Row Results

I have a very simple select query which is being used to create an input file for a piece of software. I have the query pulling all the required fields, however I need to replicate the results six times with a hard coded ID number (1,2,3,4,5,6).

I have seen CROSS APPLY and PIVOT but the problem is the column I need to use for these doesn't exist as I'm hard coded then number.

Any help would be much appreciated.

Thanks in Advance

Upvotes: 0

Views: 72

Answers (2)

MRsa
MRsa

Reputation: 684

Maybe like this:

select CJ.ID,T.* from dbo.Table T
        CROSS JOIN
        (select 1 ID UNION ALL select 2 ID UNION ALL select 3 ID UNION ALL select 4 ID UNION ALL select 5 ID UNION ALL select 6 ID) CJ

Upvotes: 1

Thom A
Thom A

Reputation: 95534

Bit of a pure guess here, but are you saying that every row in your table needs to be repeated 6 times with the ID 1-6? If so, you can use a CTE of the values 1-6 and CROSS APPLY to that.

WITH Nums AS(
    SELECT *
    FROM (VALUES (1),(2),(3),(4),(5),(6)) V(N))
SELECT *
FROM YourTable YT
     CROSS APPLY Nums;

Upvotes: 1

Related Questions