将行转换为列SQL Server，T-SQL [英] Converting rows to columns SQL Server, T-SQL

问题描述

我有一个函数，该函数产生几行作为字符串值.值如下:

I have a function that produces a few rows as string values. The values are as follows:

[12*02]
[12*03]
[12*04]
[12*05]
[12*06]
[12*07]
[12*08]
[12*09]
[12*10]
[12*11]
[12*12]
[12*13]
[12*14]
[12*15]

这些值实际上是不同的行.因此[12 * 01]是第1行，[12 * 02]是第2行，依此类推. 我想将这些值用作列标题.因此[12 * 01]是第1列，[12 * 02]是第2列，依此类推.我本来会使用数据透视表，但是此字符串每次都会不断变化，这就是我不想使用数据透视表的原因.这不是大学的作业，我只想到使用RANK和行号功能.

The values are actually different rows. So [12*01] is row 1 and [12*02] is row # 2 and so on. I want to use these values as column headers. So [12*01] is column 1 and [12*02] is column 2 and so on. I would have used a pivot but this string will keep changing every time and thats the very reason I dont want to use pivots. This is not a college homework assignment, and I have only thought of using RANK and row number functions.

任何帮助将不胜感激.

Any help would be appreciated.

推荐答案

您仍然必须使用数据透视表(或aggregate_function(CASE)手工编码的变体)，但是可以动态地进行操作.创建一个接受以逗号分隔的列标题列表的过程，并准备sql代码以在varchar变量中进行透视，在该列表应放置占位符的位置.这使您可以将数据透视表代码保存在表中，或将视图定义读入varchar变量，以帮助您进行语法检查.之后，将占位符替换为实际列表并执行数据透视.

You will still have to use pivot (or aggregate_function (CASE ) hand-coded variant), but you can do it dynamically. Create a procedure accepting a comma-separated list of column headers and prepare sql code for pivot in varchar variable placing a placeholders where a list should be. This allows you to save pivot code in a table, or read a view definition into varchar variable to help you with syntax checking. After that replace placeholders with actual list and execute pivot.

create proc ExecPivot (@PivotedList nvarchar(max))
as
   set nocount on

   declare @sql nvarchar(max)
   set @sql = N'
       select ColumnList, [PLACEHOLDER]
       from TableA
       pivot 
       (
          min(Value)
          for Key in ([PLACEHOLDER])
       ) a'
   set @sql = REPLACE(@sql, '[PLACEHOLDER]', @PivotedList)
   print @sql
   exec sp_executesql @sql

将许多行合并为一个文本有很好的参考字符串. @PivotedList参数可能需要它.

There is a good reference for concatenating many rows into a single text string. You will probably need it for @PivotedList parameter.

我希望我不完全是商标:-)

I hope I am not entirely of the mark :-)

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