SQL Server 数据透视?将列名设置为一行中的值的某种方法 [英] SQL Server pivots? some way to set column names to values within a row

问题描述

我正在构建一个由多个跟踪器组成的系统，这些跟踪器将使用许多相同的列，因此有一个用于跟踪器的表、跟踪器列，然后是当用户使用时，哪些列与哪个跟踪器一起使用的交叉引用插入跟踪行，不同的列值存储在共享相同记录 id 的多行中，并存储特定列的值和名称.

I am building a system of multiple trackers that are going to use a lot of the same columns so there is a table for the trackers, the tracker columns, then a cross reference for which columns go with which tracker, when a user inserts a tracker row the different column values are stored in multiple rows that share the same record id and store both the value and the name of the particular column.

我需要找到一种方法，将值的列名动态更改为存储在同一行中的列名.

I need to find a way to dynamically change the column name of the value to be the column name that is stored in the same row.

即

id | value | name
------------------
23 | red   | color
23 | fast  | speed

需要看起来像这样.

id | color | speed
------------------
23 | red   | fast

非常感谢任何帮助，谢谢.

Any help is greatly appreciated, thank you.

推荐答案

您可以使用 PIVOT.有两种方法，一种是您对列的值进行硬编码的静态数据透视，另一种是在运行时确定列的动态数据透视.

You can perform this type of query with a PIVOT. There are two ways, a static pivot where you hard-code the values of the columns or a dynamic pivot where the columns are determined at run-time.

静态枢轴(参见 SQL Fiddle with Demo)

select *
from 
(
  select id, value, name
  from test
) x
pivot
(
  min(value)
  for name in ([color], [speed])
) p

Dynamic Pivot(参见 SQL Fiddle with Demo)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT id,' + @cols + ' from 
         (
            SELECT id, value, name
            FROM test
         ) x
         pivot 
         (
            min(value)
            for name in (' + @cols + ')
         ) p '

execute(@query)

两个查询将产生相同的结果.不同之处在于，首先您必须对要成为列的所有值进行编码.

Both queries will produce the same results. The difference is that in the first you have to code all of the values that you want to become columns.

这篇关于SQL Server 数据透视?将列名设置为一行中的值的某种方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！