I have two columns
I want to output query like
I try to use loop in SQL but I failed to output query
My question is: how can I repeat the values in the [Name] column by the number in the [Total] column?
5
-
If you post images of code or error messages make sure you also copy and paste or type the actual code/message into the post directly - please read this.Marco Aurelio Fernandez Reyes– Marco Aurelio Fernandez Reyes2019-12-30 19:58:53 +00:00Commented Dec 30, 2019 at 19:58
-
Well, you can't use loops in plain SQL. This is available in Procedural SQL. What you need can be achieved using for example recursive query with some calculation.Kamil Gosciminski– Kamil Gosciminski2019-12-30 19:59:19 +00:00Commented Dec 30, 2019 at 19:59
-
ok how ???.......Mahmoud Magdy Mousaa– Mahmoud Magdy Mousaa2019-12-30 20:00:29 +00:00Commented Dec 30, 2019 at 20:00
-
how are you getting the initial sql result (the table that shows accumulated total)sam– sam2019-12-30 20:02:51 +00:00Commented Dec 30, 2019 at 20:02
-
ok i need I want to execute the statement more than once, according to the existing number in column [total]Mahmoud Magdy Mousaa– Mahmoud Magdy Mousaa2019-12-30 20:06:34 +00:00Commented Dec 30, 2019 at 20:06
Add a comment
|
3 Answers
This is usually handled with a recursive subquery. I derived a row order in case of duplicate names, however, that is best served using a unique key.
MS SQL Server 2017 Schema Setup:
CREATE TABLE Names(name NVARCHAR(50), total INT)
INSERT Names VALUES('ahmed',3),('mahmoud',2),('ahmed',5)
Query 1:
;WITH Normalized AS
(
SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Names
)
,ReplicateAmount AS
(
SELECT name, running_total=total, total, RowNumber
FROM Normalized
UNION ALL
SELECT R.name, running_total=(R.running_total - 1), R.total , R.RowNumber
FROM ReplicateAmount R INNER JOIN Normalized N ON R.RowNumber = N.RowNumber
WHERE R.running_total > 1
)
SELECT
name,instance=RowNumber,total=1,OriginalTotal=total,running_total
FROM
ReplicateAmount
ORDER BY
RowNumber,name,total,running_total
OPTION (MAXRECURSION 0)
| name | instance | total | OriginalTotal | running_total |
|---------|----------|-------|---------------|---------------|
| ahmed | 1 | 1 | 3 | 1 |
| ahmed | 1 | 1 | 3 | 2 |
| ahmed | 1 | 1 | 3 | 3 |
| mahmoud | 2 | 1 | 2 | 1 |
| mahmoud | 2 | 1 | 2 | 2 |
| ahmed | 3 | 1 | 5 | 1 |
| ahmed | 3 | 1 | 5 | 2 |
| ahmed | 3 | 1 | 5 | 3 |
| ahmed | 3 | 1 | 5 | 4 |
| ahmed | 3 | 1 | 5 | 5 |
3 Comments
Mahmoud Magdy Mousaa
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Mahmoud Magdy Mousaa
max 100 row >>>>>>>>
Ross Bush
Add OPTION (MAXRECURSION 0) After the query. There is a limit of 32767 recursive frames.
You can do this considerably simpler than the accepted answer.
I would suggest creating a numbers table with sequential positive integers...
CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);
INSERT INTO dbo.Numbers
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1,
sys.all_objects o2
and then just join on number <= total (as below)
SELECT name, 1 AS total
FROM Names nam
JOIN dbo.Numbers num ON num.Number <= nam.total;
If you are on 2017+ another method for "fun" is below (SQL Fiddle)
SELECT name, 1 AS total
FROM Names
CROSS APPLY STRING_SPLIT(SPACE(total - 1), ' ')
3 Comments
Mahmoud Magdy Mousaa
Msg 208, Level 16, State 1, Line 1 Invalid object name 'STRING_SPLIT'.
Ross Bush
Many of our production systems are pre SQL 2016. This does make queries less verbose, however.
Martin Smith
@MahmoudMagdyMousaa - Will add the code for the Numbers table example then
For your closed question;
CREATE TABLE #Test1(
[ID] [int] NULL,
[Cnt] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE #Test2(
[ID] [int] NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE #Test3(
[ID1] [int] NULL,
Cnt int,
Id2 int,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO #TEST1 (ID, Cnt) VALUES (1, 2)
INSERT INTO #TEST1 (ID, Cnt) VALUES (2, 3)
INSERT INTO #TEST1 (ID, Cnt) VALUES (3, 1)
INSERT INTO #TEST1 (ID, Cnt) VALUES (4, 2)
INSERT INTO #TEST1 (ID, Cnt) VALUES (5, 1)
INSERT INTO #TEST1 (ID, Cnt) VALUES (6, 4)
INSERT INTO #TEST2 (ID, Col1, Col2, Col3) VALUES (1, 'a1', 'b1', 'c1')
INSERT INTO #TEST2 (ID, Col1, Col2, Col3) VALUES (2, 'a2', 'b2', 'c2')
INSERT INTO #TEST2 (ID, Col1, Col2, Col3) VALUES (3, 'a3', 'b3', 'c3')
INSERT INTO #TEST2 (ID, Col1, Col2, Col3) VALUES (4, 'a4', 'b4', 'c4')
INSERT INTO #TEST2 (ID, Col1, Col2, Col3) VALUES (5, 'a5', 'b5', 'c5')
INSERT INTO #TEST2 (ID, Col1, Col2, Col3) VALUES (6, 'a6', 'b6', 'c6')
declare @a int = 1, @b int, @c int = 0;
while @a <= (select MAX(Id) from #Test1)
begin
set @b = (select Cnt from #Test1 where ID = @a);
set @c = 0;
while @c < @b begin
insert into #Test3 (ID1, Cnt, Id2, col1, col2, col3) select @a, @b, @a, col1, col2, col3 from #Test2 where ID = @a;
set @c += 1;
end
SET @a += 1;
end
select * from #Test3 order by 1, 2;
