查找 T-SQL 以按块转置表 [英] Find T-SQL to Transpose A Table By Chunks

问题描述

我正在寻找使用 T-SQL 执行此操作的最干净、最短、最简单和最有效的方法.我希望所有这些事情都有一个单一的方式，但我意识到它可能不是.

I am looking for the cleanest, shortest, simplest, and most efficient way to do this using T-SQL. I hope that a single way is all of those things, but I realize that it may not be.

我尝试过(未成功)使用 PIVOT，但我认为我需要的是按块进行 PIVOT 并且不聚合

I have tried (unsuccessfully) using PIVOT, but what I think I need is to PIVOT by chunks AND without aggregating

我查询了一张大表得到了这个结果:(结果按年份降序排列.数字是给定年份中以该姓名出生的人的出现次数)

I queried a large table to obtain this result: (the result is ordered by year by the Number descending. the Number is the Number of occurrences of people born with that Name in the given year)

Name    Number  Year
John    9655    1880
William 9532    1880
James   5927    1880
Charles 5348    1880
George  5126    1880
John    8769    1881
William 8524    1881
Charles 5442    1881
George  4664    1881
James   4636    1881
John    9557    1882
James   9298    1882
William 5892    1882
George  5193    1882
Charles 5092    1882

我想把上面的结果变成这样:

I want to turn the above result into this:

1880    1881    1882
John    John    John
William William James
James   Charles William
Charles George  George
George  James   Charles

推荐答案

您可以使用 PIVOT 来获得结果，您唯一需要做的就是添加一个 row_number() ，即通过按 number 排序的 year 对数据进行分区而生成.然后，当您应用聚合函数时，此唯一序列允许您为每年返回多行.此外，由于您想转换字符串数据，您需要使用 max 或 min 聚合函数:

You can use PIVOT to get the result, the only thing that you will need to do is add a row_number() that is generated by partitioning the data by year ordered by the number. This unique sequence then allows you to return multiple rows for each year when you apply the aggregate function. Also since you want to convert string data you need to use either the max or min aggregate function:

select [1880], [1881], [1882]
from
(
  select name, year,
    row_number() over(partition by year
                      order by number desc) seq
  from yourtable
) d
pivot
(
  max(name)
  for year in ([1880], [1881], [1882])
) p;

请参阅SQL Fiddle with Demo.退货:

|    1880 |    1881 |    1882 |
|---------|---------|---------|
|    John |    John |    John |
| William | William |   James |
|   James | Charles | William |
| Charles |  George |  George |
|  George |   James | Charles |

这篇关于查找 T-SQL 以按块转置表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！