扁平化/非规范化 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... >_<

只为一个表编写此查询不是最好的，但我需要为另外 30 个相同大小的表平展这样的数据.呃……请给点建议?

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，则可以使用 PIVOT 操作符来做.

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 或 交叉表.

Also see SO questions tagged pivot or crosstab.

这篇关于扁平化/非规范化 SQL 查找表的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！