SQL Duplicate Row Results

Question

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

Apologies the query was so simple I didn't think I needed to add it but as requested: SELECT 1 AS ID, [Reference], [Name], [Business Type] FROM Quarterly_Download — Carlos80
– Carlos80, Commented Nov 20, 2017 at 11:35
I asked because there are different ways depending on whether it is an aggregate query or not. — Vincent James
– Vincent James, Commented Nov 20, 2017 at 11:50

MRsa · Accepted Answer · 2017-11-20 11:33:30Z

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

Thom A · Accepted Answer · 2017-11-20 11:32:40Z

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;

Collectives™ on Stack Overflow

SQL Duplicate Row Results

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related