sql - sql - 将行转换为列
问题描述
我有以下表结构:
ID ADDRESS SALEPRICE PRICEPERAREA APPRAISALREPORTID
1 2560 W Central Ave 115000 98.46 1
2 543 N Logan 110000 94.18 1
3 321 Wall Street 115000 98.46 1
4 5441 N East Road 125000 94.65 2
5 2635 N Califnia Ave 118000 92.35 2
6 1526 W 18th Place 12000 91.54 2
我希望输出看起来像这样:
在这里,我没有转换values
为,columns
而是我希望值将成为记录的一部分。
我查看了多篇关于pivot
&的文章,cross apply
但无法理解它将如何帮助我。
关于数据的一些fatcs:
- 对于每个
APPRAISALRECORD
将始终有3 条记录。因此输出中的列数将始终为3x3=9
解决方案
您可以尝试在子查询或 CTE 中Row_number
使用窗口函数,然后通过Condition Aggregate function进行旋转。
测试DLL
CREATE TABLE T(
ID INT,
ADDRESS VARCHAR(50),
SALEPRICE INT,
PRICEPERAREA FLOAT,
APPRAISALREPORTID INT
);
INSERT INTO T VALUES (1,'2560 W Central Ave ',115000,98.46,1);
INSERT INTO T VALUES (2,'543 N Logan',110000,94.18,1);
INSERT INTO T VALUES (3,'321 Wall Street',115000,98.46,1);
INSERT INTO T VALUES (4,'5441 N East Road',125000,94.65,2);
INSERT INTO T VALUES (5,'2635 N Califnia Ave',118000,92.35,2);
INSERT INTO T VALUES (6,'1526 W 18th Place',12000 ,91.54, 2);
查询 1:
with cte as(
SELECT *,ROW_NUMBER() OVER(PARTITION BY APPRAISALREPORTID ORDER BY ID) rn
FROM T
)
SELECT APPRAISALREPORTID,
MAX(CASE WHEN rn = 1 then ADDRESS end) 'ADDRESS1',
MAX(CASE WHEN rn = 1 then SALEPRICE end) 'SALEPRICE1',
MAX(CASE WHEN rn = 1 then PRICEPERAREA end) 'PRICEPERAREA1',
MAX(CASE WHEN rn = 2 then ADDRESS end) 'ADDRESS2',
MAX(CASE WHEN rn = 2 then SALEPRICE end) 'SALEPRICE2',
MAX(CASE WHEN rn = 2 then PRICEPERAREA end) 'PRICEPERAREA2',
MAX(CASE WHEN rn = 3 then ADDRESS end) 'ADDRESS3',
MAX(CASE WHEN rn = 3 then SALEPRICE end) 'SALEPRICE3',
MAX(CASE WHEN rn = 3 then PRICEPERAREA end) 'PRICEPERAREA3'
FROM CTE
group by APPRAISALREPORTID
结果:
| APPRAISALREPORTID | ADDRESS1 | SALEPRICE1 | PRICEPERAREA1 | ADDRESS2 | SALEPRICE2 | PRICEPERAREA2 | ADDRESS3 | SALEPRICE3 | PRICEPERAREA3 |
|-------------------|---------------------|------------|---------------|---------------------|------------|---------------|-------------------|------------|---------------|
| 1 | 2560 W Central Ave | 115000 | 98.46 | 543 N Logan | 110000 | 94.18 | 321 Wall Street | 115000 | 98.46 |
| 2 | 5441 N East Road | 125000 | 94.65 | 2635 N Califnia Ave | 118000 | 92.35 | 1526 W 18th Place | 12000 | 91.54 |
推荐阅读
- silverstripe - How to override the existing method of an existing class, FlysystemAssetStore that is part of the framework in SilverStripe
- c - segmentation fault (core dumped) error in a c program for combination function
- ember.js - Ember.Js Using TransitionTo Route with dynamic segment
- java - 应用程序在第二次启动时崩溃,不会将密钥保留在内存中
- python - Python count list's values and put them in ordered list without library
- julia - How to not shadow variable in inner block in Julia?
- javascript - d3js 时间线图 - 一个矩形块用于擦洗
- html - 在 90 度文本上添加字母间距时出现 CSS 悬停状态问题
- java - 如何避免 FROM 子句中的子查询将 SQL 查询转换为休眠查询?
- gitlab - 如何在 GitLab 上将图像添加到 README 文件?