SQL将行转置为未定义的列数 [英] SQL Transpose Rows to undefined number of columns

问题描述

我有一张表格，其中包含毕业生ID，学位名称和毕业年份.该表可以包含每个毕业生的任意数量的行.

I have a table containing graduate ids, degree titles and graduation years. The table can contain any number of rows for each graduate.

我想创建一个视图，将所有行转换为所需的任意列，并在没有数据的列中输入'null'.

I would like to create a view that would transpose all the rows into as many columns as required, with 'null' being entered in columns where there is no data.

我在这里已经多次问过这个问题，但是恐怕他们要么没有被标记为已回答，要么我不明白解决方案.因此，我再次提出了这个问题，希望通过使用我的表结构可以更好地理解该解决方案.

I have seen this question asked quite a few times on here but I'm afraid they have either been not marked as answered or I don't understand the solution. So I have asked the question again in the hope that using my table structure I will better understand the solution.

非常感谢您一如既往的帮助.

Any help as always is greatly appreciated.

修改: 这不一定是视图

推荐答案

此

The generic ways of doing this is well described in this answer, but applying that to more than one column in a dynamic fashion when you want the columns in a certain order (degree1, year1, degree2, year2, etc...) can be a bit tricky.

这是一种实现方法，因为如果您查看了我上面链接的答案，我相信代码将非常不言自明，我将不做进一步解释:

This is one way to do it and as I believe the code is pretty self-explanatory if you have looked at the answer I linked above I won't explain it further:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @title_cols AS NVARCHAR(MAX)
DECLARE @year_cols AS NVARCHAR(MAX)
DECLARE @header AS NVARCHAR(MAX)

SELECT 
    @title_cols = ISNULL(@title_cols + ',','') + QUOTENAME(rn),
    @year_cols  = ISNULL(@year_cols + ',','') + QUOTENAME(CONCAT('y',rn)),
    @header = ISNULL(@header + ',','') + CONCAT('MAX(',QUOTENAME(rn) ,') AS "Degree Title ', rn, '", MAX(',QUOTENAME(CONCAT('y',rn)) ,') AS "Graduation Year ', rn, '"')
FROM (
  SELECT DISTINCT 
    rn = ROW_NUMBER() OVER (PARTITION BY [Graduate ID] ORDER BY [Degree Title], [Graduation Year]) 
  FROM Graduates
) GRADS

SET @sql =
  N'SELECT [Graduate ID], ' + @header + '
    FROM (
       SELECT *,
          title = ROW_NUMBER() OVER (PARTITION BY [Graduate ID] ORDER BY [Graduation Year]),
          year = CONCAT(''y'',ROW_NUMBER() OVER (PARTITION BY [Graduate ID] ORDER BY [Graduation Year])) 
       FROM Graduates) g
    PIVOT(MAX([Degree Title]) FOR title IN (' + @title_cols + ')) AS Titles
    PIVOT(MAX([Graduation Year]) FOR year IN (' + @year_cols + ')) AS Years
    GROUP BY [Graduate ID]'

EXEC sp_executesql @sql

示例SQL提琴

查询使用SQL Server 2012及更高版本中提供的concat()函数，因此，如果您使用的是旧版本，则必须将该部分更改为带有强制类型转换的常规"字符串连接.

The query uses the concat() function which is available in SQL Server 2012 and later, so if you are using an older version you would have to change that part to a "normal" string concatenation with casts.

我确定查询可以通过多种方式进行改进，但我将保留它作为练习:)

I'm sure the query can be improved in many ways, but I'll leave that as an exercise :)

这篇关于SQL将行转置为未定义的列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！