Styjun

Why would an airline put 15 passengers at once on standby?

Whaling ship logistics

Sci-fi movie with one survivor and an organism(?) recreating his memories

Is there no "respectively" in german language when listing (enumerating) something?

What does `idem` mean in the VIM docs?

Assembly of PCBs containing a mix of SMT and thru-hole parts?

Why isn't there armor to protect from spells in the Potterverse?

How many stack cables would be needed if we want to stack two 3850 switches

What would influence an alien race to map their planet in a way other than the traditional map of the Earth

How can I become an invalid target for spells that target humanoids?

LM324 - Issue with output in negative feedback

Is the illusion created by Invoke Duplicity affected by difficult terrain?

I transpose the source code, you transpose the input!

How to justify getting additional team member when the current team is doing well?

Does the app TikTok violate trademark?

Concerning a relationship in the team

Create the same subfolders in another folder

If a spaceship ran out of fuel somewhere in space between Earth and Mars, does it slowly drift off to the Sun?

How to prevent pickpocketing in busy bars?

Garage door sticks on a bolt

Windows 10 deletes lots of tiny files super slowly. Anything that can be done to speed it up?

Why is STARTTLS still used?

Is there a list of world wide upcoming space events on the web?

As a team leader is it appropriate to bring in fundraiser candy?

Itemize/Disaggregate Aggregate Data Table

disaggregate summarised table in SQL Server 2008Add a column with a default value to an existing table in SQL ServerHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerFinding the index of an item given a list containing it in PythonHow to randomly select an item from a list?Table Naming Dilemma: Singular vs. Plural NamesInsert results of a stored procedure into a temporary tableHow can I count the occurrences of a list item?SQL Server SELECT INTO @variable?Get size of all tables in database

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

0

I have a table of aggregate data:

KWid Orders Revenue
12345 10 150
23468 5 200

This data is stored in one vendor's reports, but we are migrating to a new vendor. To ingest the transactional data, the new vendor needs each order listed on a single line, i.e. KWid 23468 with 5 orders would show as follows:

KWid Orders Revenue OrderID
23468 1 40 1
23468 1 40 2
23468 1 40 3
23468 1 40 4
23468 1 40 5

Note, the total revenue for the 5 orders (200) was split equally across the 5 lines. Also, a unique order ID has to be created for each individual row.

I have done this in the past, but can't seem to locate my code anywhere. How would I accomplish with the following stipulations:

1. Must by in SQL Server or Python


2. Cannot include a counter table as shown in this solution (too many transactions to create such a table efficiently) - disaggregate summarised table in SQL Server 2008
   

python sql-server aggregate

share|improve this question

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just join to a tally table and this is painless.
  
  – Sean Lange
  Mar 28 at 19:05
  

  
  
  
  

add a comment
 | 

0

I have a table of aggregate data:

KWid Orders Revenue
12345 10 150
23468 5 200

This data is stored in one vendor's reports, but we are migrating to a new vendor. To ingest the transactional data, the new vendor needs each order listed on a single line, i.e. KWid 23468 with 5 orders would show as follows:

KWid Orders Revenue OrderID
23468 1 40 1
23468 1 40 2
23468 1 40 3
23468 1 40 4
23468 1 40 5

Note, the total revenue for the 5 orders (200) was split equally across the 5 lines. Also, a unique order ID has to be created for each individual row.

I have done this in the past, but can't seem to locate my code anywhere. How would I accomplish with the following stipulations:

1. Must by in SQL Server or Python


2. Cannot include a counter table as shown in this solution (too many transactions to create such a table efficiently) - disaggregate summarised table in SQL Server 2008
   

python sql-server aggregate

share|improve this question

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just join to a tally table and this is painless.
  
  – Sean Lange
  Mar 28 at 19:05
  

  
  
  
  

add a comment
 | 

I have a table of aggregate data:

KWid Orders Revenue
12345 10 150
23468 5 200

This data is stored in one vendor's reports, but we are migrating to a new vendor. To ingest the transactional data, the new vendor needs each order listed on a single line, i.e. KWid 23468 with 5 orders would show as follows:

KWid Orders Revenue OrderID
23468 1 40 1
23468 1 40 2
23468 1 40 3
23468 1 40 4
23468 1 40 5

Note, the total revenue for the 5 orders (200) was split equally across the 5 lines. Also, a unique order ID has to be created for each individual row.

I have done this in the past, but can't seem to locate my code anywhere. How would I accomplish with the following stipulations:

1. Must by in SQL Server or Python


2. Cannot include a counter table as shown in this solution (too many transactions to create such a table efficiently) - disaggregate summarised table in SQL Server 2008
   

python sql-server aggregate

share|improve this question

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

KWid Orders Revenue
12345 10 150
23468 5 200

KWid Orders Revenue OrderID
23468 1 40 1
23468 1 40 2
23468 1 40 3
23468 1 40 4
23468 1 40 5

python sql-server aggregate

python sql-server aggregate

share|improve this question

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

share|improve this question

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

edited Mar 28 at 19:14

marchoy

14511 silver badge88 bronze badges

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

asked Mar 28 at 18:53

Matt DMatt D

611 bronze badge

1 Answer
1

active

oldest

votes

1

Joining to a tally table makes this pretty straight forward. You can read more about tally tables here.

I keep one as a view on my system. Here is my version.

create View [dbo].[cteTally] as

WITH
 E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS 
 (
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 )
select N from cteTally
GO

Next we need a table with your table so I just tossed together quick table variable. In your query you would write this against your base table.

declare @Something table (KWid int, Orders int, Revenue decimal(7,2))

insert @Something values
 (12345, 10, 150)
 , (23468, 5, 200)

Now we have the problem setup and this becomes fairly easy. This query should produce the data you stated you want.

select s.KWid
 , OrderNum = t.N 
 , Revenue = convert(numeric(7, 2), round(s.Revenue / s.Orders, 2))
from @Something s
join cteTally t on t.N <= s.Orders
order by s.KWid
 , t.N

share|improve this answer

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That is an absolutely brilliant solution! Thanks Sean!
  
  – Matt D
  Mar 28 at 22:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Cool glad you like it. If this works you should consider marking this as the answer so others know this solved your problem.
  
  – Sean Lange
  Mar 29 at 13:04
  

  
  
  
  

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%2f55404953%2fitemize-disaggregate-aggregate-data-table%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Joining to a tally table makes this pretty straight forward. You can read more about tally tables here.

I keep one as a view on my system. Here is my version.

create View [dbo].[cteTally] as

WITH
 E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS 
 (
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 )
select N from cteTally
GO

Next we need a table with your table so I just tossed together quick table variable. In your query you would write this against your base table.

declare @Something table (KWid int, Orders int, Revenue decimal(7,2))

insert @Something values
 (12345, 10, 150)
 , (23468, 5, 200)

Now we have the problem setup and this becomes fairly easy. This query should produce the data you stated you want.

select s.KWid
 , OrderNum = t.N 
 , Revenue = convert(numeric(7, 2), round(s.Revenue / s.Orders, 2))
from @Something s
join cteTally t on t.N <= s.Orders
order by s.KWid
 , t.N

share|improve this answer

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That is an absolutely brilliant solution! Thanks Sean!
  
  – Matt D
  Mar 28 at 22:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Cool glad you like it. If this works you should consider marking this as the answer so others know this solved your problem.
  
  – Sean Lange
  Mar 29 at 13:04
  

  
  
  
  

add a comment
 | 

1

Joining to a tally table makes this pretty straight forward. You can read more about tally tables here.

I keep one as a view on my system. Here is my version.

create View [dbo].[cteTally] as

WITH
 E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS 
 (
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 )
select N from cteTally
GO

Next we need a table with your table so I just tossed together quick table variable. In your query you would write this against your base table.

declare @Something table (KWid int, Orders int, Revenue decimal(7,2))

insert @Something values
 (12345, 10, 150)
 , (23468, 5, 200)

Now we have the problem setup and this becomes fairly easy. This query should produce the data you stated you want.

select s.KWid
 , OrderNum = t.N 
 , Revenue = convert(numeric(7, 2), round(s.Revenue / s.Orders, 2))
from @Something s
join cteTally t on t.N <= s.Orders
order by s.KWid
 , t.N

share|improve this answer

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That is an absolutely brilliant solution! Thanks Sean!
  
  – Matt D
  Mar 28 at 22:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Cool glad you like it. If this works you should consider marking this as the answer so others know this solved your problem.
  
  – Sean Lange
  Mar 29 at 13:04
  

  
  
  
  

add a comment
 | 

Joining to a tally table makes this pretty straight forward. You can read more about tally tables here.

I keep one as a view on my system. Here is my version.

create View [dbo].[cteTally] as

WITH
 E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS 
 (
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 )
select N from cteTally
GO

Next we need a table with your table so I just tossed together quick table variable. In your query you would write this against your base table.

declare @Something table (KWid int, Orders int, Revenue decimal(7,2))

insert @Something values
 (12345, 10, 150)
 , (23468, 5, 200)

Now we have the problem setup and this becomes fairly easy. This query should produce the data you stated you want.

select s.KWid
 , OrderNum = t.N 
 , Revenue = convert(numeric(7, 2), round(s.Revenue / s.Orders, 2))
from @Something s
join cteTally t on t.N <= s.Orders
order by s.KWid
 , t.N

share|improve this answer

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges

create View [dbo].[cteTally] as

WITH
 E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS 
 (
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 )
select N from cteTally
GO

declare @Something table (KWid int, Orders int, Revenue decimal(7,2))

insert @Something values
 (12345, 10, 150)
 , (23468, 5, 200)

select s.KWid
 , OrderNum = t.N 
 , Revenue = convert(numeric(7, 2), round(s.Revenue / s.Orders, 2))
from @Something s
join cteTally t on t.N <= s.Orders
order by s.KWid
 , t.N

share|improve this answer

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges

answered Mar 28 at 19:36

Sean LangeSean Lange

27.8k22 gold badges2020 silver badges3535 bronze badges