英文:
SQL Server transpose rows to column
问题
我们有这个表格：
| 列A | 列B |
|---|---|
| 01-01-2020 | 23 |
| 01-01-2020 | 24 |
| 01-01-2020 | 25 |
| 02-01-2020 | 11 |
| 02-01-2020 | 15 |
要求是基于日期时间列将数据加载到单行中。
| 列A | 列B | 列C | 列D |
|---|---|---|---|
| 01-01-2020 | 23 | 24 | 25 |
| 02-01-2020 | 11 | 15 |
对于单个日期时间值，最多可以有200个唯一值。
如何在查询中实现这一目标？
我已经尝试使用旋转（pivot），但结果是聚合，这不是我想要的。
英文:
We have this table:
| Column A | Column B |
|---|---|
| 01-01-2020 | 23 |
| 01-01-2020 | 24 |
| 01-01-2020 | 25 |
| 02-01-2020 | 11 |
| 02-01-2020 | 15 |
The requirement is to load the data in single row based on datetime column.
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| 01-01-2020 | 23 | 24 | 25 |
| 02-01-2020 | 11 | 15 |
There can be up to 200 unique values against single datetime value.
How can this be achieved in a query?
I have tried using pivot but that results is aggregation which is not what I am after.
答案1
得分: 0
由于您最多有200列，我认为最好给它们编号。您可以扩展到所需的列数。
示例
Select *
From (
Select [Column A]
,[Column B]
,RN = row_number() over (partition by [Column A] order by [Column B] )
From YourTable
) src
Pivot ( max([Column B]) for RN in ( [1], [2], [3], [4], [5], [6], [7], [8], [9],[10]
,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
) ) Pvt
英文:
Since you have up to 200 hundred columns, I figured it would be best to number them. You can expand up to the required number of columns
Example
Select *
From (
Select [Column A]
,[Column B]
,RN = row_number() over (partition by [Column A] order by [Column B] )
From YourTable
) src
Pivot ( max([Column B]) for RN in ( [1], [2], [3], [4], [5], [6], [7], [8], [9],[10]
,[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
,[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
) ) Pvt
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论