展平/反规范化SQL查找表的最佳方法? [英] Best way to flatten/denormalize SQL lookup tables?

问题描述

我有一堆这样的表:

Lookup_HealthCheckupRisks
------------
ID  Name
1   Anemia
2   Anorexic
3   Bulemic
4   Depression
...
122   Syphilis



PatientRisksOnCheckup
------------------
ID CheckupID RiskID
1  11        2
2  11        3
3  12        1
4  14        1
5  14        3
...

但是我需要一个扁平化的版本，像这样:

But I need a flattened version, like this:

PatientCheckup
------------------
CheckupID Risk_1 Risk_2 Risk_3 Risk_4 .. Risk_122
11        0      1      1      0         0
12        1      0      0      0         0
13        0      0      0      0         0
14        1      0      1      0         0

我不知道如何执行此操作，我能想到的最好的办法是编写一个临时表，定义所有122列，然后执行If Exists ( SELECT * FROM PatientRisksOnCheckup where RiskID=i and checkupID=j ) INSERT INTO PatientCheckup (1) WHERE CheckupID=j并遍历i, j...> _<

I'm clueless how to do this, the best I can think of is to write a temp table, define all 122 columns, and then do If Exists ( SELECT * FROM PatientRisksOnCheckup where RiskID=i and checkupID=j ) INSERT INTO PatientCheckup (1) WHERE CheckupID=j and iterate overi, j... >_<

只为一个表编写此查询并不是最好的方法，但是我需要像这样对另外三十个相同大小的表进行数据展平.嗯...有什么建议吗?

Writing this query for just one table is doable not the best, but I've need to flatten data like this for another thirty tables of the same size. Er... suggestions please?

我也很想知道我在做什么是否是一件普通的事情...?

I am also curious to know if what I am doing is a common thing to do or not... ?

我需要对统计软件的sql数据进行归一化/展平.

I am needing to denormalize/flatten the sql data for statistics software.

推荐答案

您需要的称为交叉表查询.

What you need is called a crosstab query.

如果您使用的是Microsoft SQL Server，则可以使用 运算符即可完成.

If you're using Microsoft SQL Server, you can use the PIVOT operator to do it.

其他品牌的RDBMS对这种类型的查询有不同的支持.最糟糕的情况是，您将不得不使用动态SQL将非常有价值的值从查找表硬编码到主表的联接中.当您有122个不同的值时，这是不切实际的.

Other brands of RDBMS have varying support for this type of query. Worst case is you'll have to use dynamic SQL to hard-code very value from the lookup table into a join to your main table. This is not practical when you have 122 distinct values.

还可以看到标记为 pivot 或 查看全文