时间戳

首页 > 解决方案 > SQL 中的多列细分（取消分组依据）

sql - SQL 中的多列细分（取消分组依据）

问题描述

我的新数据grouped by来自它的源，因此是否有任何代码可以分解或取消分组数据，然后根据分组的列分配其值。

桌子看起来像这样

| age | education | city | male | female

| 28  | Secondary |  x   |  0   |   2
| 28  | University|  x   |  1   |   2
| 29  | Primary   |  y   |  1   |   0

我希望代码做这样的事情

| age | education | city | gender |

| 28  | Secondary |  x   | female |
| 28  | Secondary |  x   | female | 
| 28  | University|  x   |  male  |
| 28  | University|  x   | female |
| 28  | University|  x   | female |
| 29  | Primary   |  y   |  male  |

标签： sqlsql-server

解决方案

简单的方法UNION ALL是CROSS APPLY

SELECT M.age,education,city,'Male' as Gender
FROM #TAB M
CROSS APPLY
(
    SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER BETWEEN 1 AND M.MALE
)MALE

UNION ALL

SELECT F.age,education,city,'Female' as Gender
FROM #TAB F
CROSS APPLY
(
    SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER BETWEEN 1 AND F.FEMALE
)MALE

推荐阅读