Styjun

SQL Server table with 4,000,000 rows is 40GB

How can "life" insurance prevent the cheapening of death?

Why would "an mule" be used instead of "a mule"?

How does Vivi differ from other Black Mages?

Is there a "right" way to interpret a novel? If so, how do we make sure our novel is interpreted correctly?

Are there take-over requests in aviation?

Creating a Master Image to roll out to 30 new Machines Licensing Issues

SCOTUS - Can Congress overrule Marbury v. Madison by statute?

RP Automatic Updates

What is this dollar sign ($) icon in my Menu Bar?

Can i say "I will encrypt something" if i hash something?

Starring Samurais - Several Scribbled Short Stories

For how long could UK opposition parties prevent new elections?

Which ping implementation is cygwin using?

My favorite color is blue what is your favorite color?

Should I use my toaster oven for slow roasting?

Are programming languages necessary/useful for operations research practitioner?

How much power do LED smart bulb wireless control systems consume when the light is turned off?

Is there a basic list of ways in which a low-level Rogue can get advantage for sneak attack?

Writing a love interest for my hero

I changed a word from a source, how do I cite it correctly?

What causes this bloom with an old telephoto lens?

Could the government trigger by-elections to regain a majority?

I see your BIDMAS and raise you a BADMIS

Repeating rows based on the frequency

Add a column with a default value to an existing table in SQL ServerHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?Can I concatenate multiple MySQL rows into one field?Table Naming Dilemma: Singular vs. Plural NamesInserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableHow do I UPDATE from a SELECT in SQL Server?Select first row in each GROUP BY group?Find all tables containing column with specified name - MS SQL Server

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

-2

1

I have a table with 2 columns named A and B which is defined as :

A B
c1 2
c2 3
c3 4

The expected output is :

A B
c1 1
c1 2 
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

sql sql-server self-join

share|improve this question

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  The question doesn't appear to include any attempt at all to solve the problem. StackOverflow expects you to try to solve your own problem first, as your attempts help us to better understand what you want. Please edit the question to show what you've tried, and show a specific roadblock you're running into with Minimal, Complete, and Verifiable example. For more information, please see How to Ask.
  
  – Andreas
  Mar 28 at 7:30
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  This is unclear. But if it were clear it would be a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title.
  
  – philipxy
  Mar 28 at 7:33
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Expected output of what?
  
  – DaveInCaz
  Mar 28 at 11:32
  

  
  
  
  

add a comment | 

-2

1

I have a table with 2 columns named A and B which is defined as :

A B
c1 2
c2 3
c3 4

The expected output is :

A B
c1 1
c1 2 
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

sql sql-server self-join

share|improve this question

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  The question doesn't appear to include any attempt at all to solve the problem. StackOverflow expects you to try to solve your own problem first, as your attempts help us to better understand what you want. Please edit the question to show what you've tried, and show a specific roadblock you're running into with Minimal, Complete, and Verifiable example. For more information, please see How to Ask.
  
  – Andreas
  Mar 28 at 7:30
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  This is unclear. But if it were clear it would be a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title.
  
  – philipxy
  Mar 28 at 7:33
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Expected output of what?
  
  – DaveInCaz
  Mar 28 at 11:32
  

  
  
  
  

add a comment | 

I have a table with 2 columns named A and B which is defined as :

A B
c1 2
c2 3
c3 4

The expected output is :

A B
c1 1
c1 2 
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

sql sql-server self-join

share|improve this question

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

A B
c1 2
c2 3
c3 4

A B
c1 1
c1 2 
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

sql sql-server self-join

sql sql-server self-join

share|improve this question

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

share|improve this question

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

edited Mar 28 at 7:56

Michał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

asked Mar 28 at 7:25

Arpit TiwariArpit Tiwari

2122 bronze badges

4 Answers
4

active

oldest

votes

1

CREATE TABLE #table2
 ([A] varchar(2), [B] int)
;

INSERT INTO #table2
 ([A], [B])
VALUES
 ('c1', 2),
 ('c2', 3),
 ('c3', 4)

;WITH nums AS
 (SELECT 1 AS value ,a,b from #table2
 UNION ALL
 SELECT value + 1 AS value ,A,b
 FROM nums
 WHERE nums.value <B)
 SELECT a,value
 FROM nums order by a,value

output

a value
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

add a comment | 

0

Created a Table valued function in which i used Recursive cte to evaluate recurring value of given in put Then Join the table with function using Cross Apply

CREATE FUNCTION [dbo].[udf_GetData] (
 @Data INT
 )
RETURNS @output TABLE (
 Data INT

 )

BEGIN 
 ;WITH CTe
 AS
 (
 SELECT 1 As Data 
 UNION ALL
 SELECT Data+1
 FROM CTe
 WHERE Data < @Data
 )
 INSERT INTO @output
 SELECT Data FROM CTe

 RETURN
END

Sample Data and explained How to call function using CROSS APPLY

 DECLARE @Data AS TABLE (A VARCHAR(10),B INT)
 INSERT INTO @Data

 SELECT 'c1', 2 UNION ALL
 SELECT 'c2', 3 UNION ALL
 SELECT 'c3', 4

 SELECT d.A,
 (SELECT [dbo].[udf_GetData](d.B)) AS RecData
 FROM @Data d

Result

A RecursiveData
----------------
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

add a comment | 

0

You could try this:

// test data
declare @tbl table(A char(2), B int);
insert into @tbl values
('c1', 2),
('c2', 3),
('c3', 4);
// create CTE with numbers which we will need to join
declare @max int;
select @max = max(B) from @tbl;
;with numbers as (
 select 1 n
 union all
 select n + 1 from numbers
 where n < @max
)
// join numbers with your table
select A, n from @tbl t
join numbers n on t.B >= n.n 
order by A, n

share|improve this answer

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

add a comment | 

0

Say, your table name is test.

WITH r(a, b, repeat) as 
(SELECT a, b, 1 from test
union all
select a, b, repeat+1 from r
where r.repeat < r.b)
select * from r
ORDER BY a, repeat;

share|improve this answer

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55392150%2frepeating-rows-based-on-the-frequency%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

CREATE TABLE #table2
 ([A] varchar(2), [B] int)
;

INSERT INTO #table2
 ([A], [B])
VALUES
 ('c1', 2),
 ('c2', 3),
 ('c3', 4)

;WITH nums AS
 (SELECT 1 AS value ,a,b from #table2
 UNION ALL
 SELECT value + 1 AS value ,A,b
 FROM nums
 WHERE nums.value <B)
 SELECT a,value
 FROM nums order by a,value

output

a value
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

add a comment | 

1

CREATE TABLE #table2
 ([A] varchar(2), [B] int)
;

INSERT INTO #table2
 ([A], [B])
VALUES
 ('c1', 2),
 ('c2', 3),
 ('c3', 4)

;WITH nums AS
 (SELECT 1 AS value ,a,b from #table2
 UNION ALL
 SELECT value + 1 AS value ,A,b
 FROM nums
 WHERE nums.value <B)
 SELECT a,value
 FROM nums order by a,value

output

a value
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

add a comment | 

CREATE TABLE #table2
 ([A] varchar(2), [B] int)
;

INSERT INTO #table2
 ([A], [B])
VALUES
 ('c1', 2),
 ('c2', 3),
 ('c3', 4)

;WITH nums AS
 (SELECT 1 AS value ,a,b from #table2
 UNION ALL
 SELECT value + 1 AS value ,A,b
 FROM nums
 WHERE nums.value <B)
 SELECT a,value
 FROM nums order by a,value

output

a value
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

CREATE TABLE #table2
 ([A] varchar(2), [B] int)
;

INSERT INTO #table2
 ([A], [B])
VALUES
 ('c1', 2),
 ('c2', 3),
 ('c3', 4)

;WITH nums AS
 (SELECT 1 AS value ,a,b from #table2
 UNION ALL
 SELECT value + 1 AS value ,A,b
 FROM nums
 WHERE nums.value <B)
 SELECT a,value
 FROM nums order by a,value

a value
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

answered Mar 28 at 7:33

ChanukyaChanukya

5,09011 gold badge1313 silver badges2929 bronze badges

0

Created a Table valued function in which i used Recursive cte to evaluate recurring value of given in put Then Join the table with function using Cross Apply

CREATE FUNCTION [dbo].[udf_GetData] (
 @Data INT
 )
RETURNS @output TABLE (
 Data INT

 )

BEGIN 
 ;WITH CTe
 AS
 (
 SELECT 1 As Data 
 UNION ALL
 SELECT Data+1
 FROM CTe
 WHERE Data < @Data
 )
 INSERT INTO @output
 SELECT Data FROM CTe

 RETURN
END

Sample Data and explained How to call function using CROSS APPLY

 DECLARE @Data AS TABLE (A VARCHAR(10),B INT)
 INSERT INTO @Data

 SELECT 'c1', 2 UNION ALL
 SELECT 'c2', 3 UNION ALL
 SELECT 'c3', 4

 SELECT d.A,
 (SELECT [dbo].[udf_GetData](d.B)) AS RecData
 FROM @Data d

Result

A RecursiveData
----------------
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

add a comment | 

0

Created a Table valued function in which i used Recursive cte to evaluate recurring value of given in put Then Join the table with function using Cross Apply

CREATE FUNCTION [dbo].[udf_GetData] (
 @Data INT
 )
RETURNS @output TABLE (
 Data INT

 )

BEGIN 
 ;WITH CTe
 AS
 (
 SELECT 1 As Data 
 UNION ALL
 SELECT Data+1
 FROM CTe
 WHERE Data < @Data
 )
 INSERT INTO @output
 SELECT Data FROM CTe

 RETURN
END

Sample Data and explained How to call function using CROSS APPLY

 DECLARE @Data AS TABLE (A VARCHAR(10),B INT)
 INSERT INTO @Data

 SELECT 'c1', 2 UNION ALL
 SELECT 'c2', 3 UNION ALL
 SELECT 'c3', 4

 SELECT d.A,
 (SELECT [dbo].[udf_GetData](d.B)) AS RecData
 FROM @Data d

Result

A RecursiveData
----------------
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

add a comment | 

Created a Table valued function in which i used Recursive cte to evaluate recurring value of given in put Then Join the table with function using Cross Apply

CREATE FUNCTION [dbo].[udf_GetData] (
 @Data INT
 )
RETURNS @output TABLE (
 Data INT

 )

BEGIN 
 ;WITH CTe
 AS
 (
 SELECT 1 As Data 
 UNION ALL
 SELECT Data+1
 FROM CTe
 WHERE Data < @Data
 )
 INSERT INTO @output
 SELECT Data FROM CTe

 RETURN
END

Sample Data and explained How to call function using CROSS APPLY

 DECLARE @Data AS TABLE (A VARCHAR(10),B INT)
 INSERT INTO @Data

 SELECT 'c1', 2 UNION ALL
 SELECT 'c2', 3 UNION ALL
 SELECT 'c3', 4

 SELECT d.A,
 (SELECT [dbo].[udf_GetData](d.B)) AS RecData
 FROM @Data d

Result

A RecursiveData
----------------
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

CREATE FUNCTION [dbo].[udf_GetData] (
 @Data INT
 )
RETURNS @output TABLE (
 Data INT

 )

BEGIN 
 ;WITH CTe
 AS
 (
 SELECT 1 As Data 
 UNION ALL
 SELECT Data+1
 FROM CTe
 WHERE Data < @Data
 )
 INSERT INTO @output
 SELECT Data FROM CTe

 RETURN
END

 DECLARE @Data AS TABLE (A VARCHAR(10),B INT)
 INSERT INTO @Data

 SELECT 'c1', 2 UNION ALL
 SELECT 'c2', 3 UNION ALL
 SELECT 'c3', 4

 SELECT d.A,
 (SELECT [dbo].[udf_GetData](d.B)) AS RecData
 FROM @Data d

A RecursiveData
----------------
c1 1
c1 2
c2 1
c2 2
c2 3
c3 1
c3 2
c3 3
c3 4

share|improve this answer

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

answered Mar 28 at 7:46

Sreenu131Sreenu131

2,12411 gold badge33 silver badges1414 bronze badges

0

You could try this:

// test data
declare @tbl table(A char(2), B int);
insert into @tbl values
('c1', 2),
('c2', 3),
('c3', 4);
// create CTE with numbers which we will need to join
declare @max int;
select @max = max(B) from @tbl;
;with numbers as (
 select 1 n
 union all
 select n + 1 from numbers
 where n < @max
)
// join numbers with your table
select A, n from @tbl t
join numbers n on t.B >= n.n 
order by A, n

share|improve this answer

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

add a comment | 

0

You could try this:

// test data
declare @tbl table(A char(2), B int);
insert into @tbl values
('c1', 2),
('c2', 3),
('c3', 4);
// create CTE with numbers which we will need to join
declare @max int;
select @max = max(B) from @tbl;
;with numbers as (
 select 1 n
 union all
 select n + 1 from numbers
 where n < @max
)
// join numbers with your table
select A, n from @tbl t
join numbers n on t.B >= n.n 
order by A, n

share|improve this answer

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

add a comment | 

You could try this:

// test data
declare @tbl table(A char(2), B int);
insert into @tbl values
('c1', 2),
('c2', 3),
('c3', 4);
// create CTE with numbers which we will need to join
declare @max int;
select @max = max(B) from @tbl;
;with numbers as (
 select 1 n
 union all
 select n + 1 from numbers
 where n < @max
)
// join numbers with your table
select A, n from @tbl t
join numbers n on t.B >= n.n 
order by A, n

share|improve this answer

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

// test data
declare @tbl table(A char(2), B int);
insert into @tbl values
('c1', 2),
('c2', 3),
('c3', 4);
// create CTE with numbers which we will need to join
declare @max int;
select @max = max(B) from @tbl;
;with numbers as (
 select 1 n
 union all
 select n + 1 from numbers
 where n < @max
)
// join numbers with your table
select A, n from @tbl t
join numbers n on t.B >= n.n 
order by A, n

share|improve this answer

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

answered Mar 28 at 7:53

Michał TurczynMichał Turczyn

20.5k1313 gold badges2424 silver badges4242 bronze badges

0

Say, your table name is test.

WITH r(a, b, repeat) as 
(SELECT a, b, 1 from test
union all
select a, b, repeat+1 from r
where r.repeat < r.b)
select * from r
ORDER BY a, repeat;

share|improve this answer

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges

add a comment | 

0

Say, your table name is test.

WITH r(a, b, repeat) as 
(SELECT a, b, 1 from test
union all
select a, b, repeat+1 from r
where r.repeat < r.b)
select * from r
ORDER BY a, repeat;

share|improve this answer

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges

add a comment | 

Say, your table name is test.

WITH r(a, b, repeat) as 
(SELECT a, b, 1 from test
union all
select a, b, repeat+1 from r
where r.repeat < r.b)
select * from r
ORDER BY a, repeat;

share|improve this answer

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges

WITH r(a, b, repeat) as 
(SELECT a, b, 1 from test
union all
select a, b, repeat+1 from r
where r.repeat < r.b)
select * from r
ORDER BY a, repeat;

share|improve this answer

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges

answered Mar 28 at 8:31

riyaBriyaB

4988 bronze badges