SQL Server 2012 中的 PIVOT - SQL 的新功能

本文介绍了SQL Server 2012 中的 PIVOT - SQL 的新功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在下一种情况下遇到了一些麻烦:我需要为语句选择 4 个位置，并且我需要将这些位置作为列进行透视.数据库的结构为:StatementId 1、PositionId 2、RepCurrValue 3.例如，StatementId = 55 并且位置 = 58 OR 62 OR 67 OR 82，结果是:

I am having some trouble with the next situation: I need to select 4 position for a statement and I need this positions to be pivoted as columns. The database is structured as: StatementId 1, PositionId 2, RepCurrValue 3. For example for StatementId = 55 and for the positions = 58 OR 62 OR 67 OR 82 the result is:

1   2   3
-----------------
55  58  146,8000
55  62  59,9800
55  67  800,0500
55  82  136,7600

我想要它

1  58       62      67       82
---------------------------------------
55 146,8000 59,9800 800,0500 136,7600

非常感谢您的支持.

推荐答案

我用 PIVOT 表做了这个练习，可以帮助你，考虑到列标题等于字段 [2] 的内容并且不提供重复值在 [2] 列中:

I did this exercise with PIVOT table that could be help you, considering the column headings equal to the contents of the field [2] and NOT providing repeated values in column [2]:

DECLARE @cols AS NVARCHAR(MAX)

SET @cols =  STUFF((SELECT ',[' + convert(nvarchar,(t.[2])) + ']'  AS ID
 FROM TB_1 t
  FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1, '')

DECLARE  @query  AS NVARCHAR(MAX);

SET @query = N'SELECT p.[1],' + @cols + N' from
             (
                SELECT [1],[2],[3] FROM TB_1
            ) x
            pivot
            (
                max([3])
                for [2] in (' + @cols + N')
            ) p

            '
exec sp_executesql @query;

在通过添加额外的 StatementId (56) 和与 StatementId (56) 关联的新不同位置 (90,91) 获得的结果下方

below the result obtained by adding an additional StatementId (56) and new different positions (90,91) associated with StatementId (56)

1   58          62      67          82          90      91
55  146,8000    59,9800 800,0500    136,7600    NULL    NULL
56  NULL        NULL    NULL        NULL        185,74  185,74

非动态解决方案可以是:

a non-dynamic solution can be:

select *
from
(
   SELECT [1],[2],[3] FROM TB_1
) x
pivot
(
  max([3])
  for [2] in ([58] , [62] , [67] , [82] )
) p

这篇关于SQL Server 2012 中的 PIVOT - SQL 的新功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！