SQL Server枢轴？某些方法可以将列名称设置为一行中的值 [英] SQL Server pivots? some way to set column names to values within a row

问题描述

我正在构建一个多个跟踪器的系统，这些系统将使用很多相同的列，所以有一个跟踪器表，跟踪器列，然后一个交叉引用，哪个列与哪个跟踪器一起，当一个用户插入跟踪器行不同的列值存储在共享相同记录标识的多行中，并存储特定列的值和名称。

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.

ie

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

动态数据透视（见 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屋！