在SQL Server 2008中分解汇总表 [英] disaggregate summarised table in SQL Server 2008

问题描述

我已经从外部来源接收到了汇总格式的数据.我需要一种将其分解为适合我正在使用的系统的方法.

I've received data from an external source, which is in a summarised format. I need a way to disaggregate this to fit into a system I am using.

为说明起见，假设我收到的数据如下所示:

To illustrate, suppose the data I received looks like this:

receivedTable:

Age     Gender     Count
40      M          3
41      M          2

我希望这是一种分解的格式，如下所示:

I want this is a disaggregated format like this:

systemTable:

ID      Age        Gender
1       40         M          
2       40         M 
3       40         M 
4       41         M          
5       41         M 

谢谢
卡尔

推荐答案

根据计数范围，您可以使用一个查找表，该表精确地为每个整数x保存x个记录.像这样:

Depending of the range of your count you could use a lookup table that holds exactly x records for each integer x. Like this:

create table counter(num int)
insert into counter select 1

insert into counter select 2
insert into counter select 2

insert into counter select 3
insert into counter select 3
insert into counter select 3

insert into counter select 4
insert into counter select 4
insert into counter select 4
insert into counter select 4

然后加入该表:

create table source(age int, gender char(1), num int)
insert into source select 40, 'm', 3
insert into source select 30, 'f', 2
insert into source select 20, 'm', 1

--insert into destination(age, gender)
    select age, gender
    from source
        inner join counter on counter.num = source.num

这篇关于在SQL Server 2008中分解汇总表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！