使用数据透视表或选择大小写 [英] Using Pivot table or Select case

问题描述

我有一个包含15个字段的表，并且在获取其值而不是行时，我希望将它们显示在列中.所以我正在使用数据透视.但是数据透视表使用聚合方法进行绑定，因此我需要表中的两个值.所以我做了两个功能.我可以通过使用switch case语句来实现相同的目的.请建议哪个是最好的，或者有其他方法可以做到.

这是我的代码

我的第一个功能

I have a table which has 15 fields and while fetching its value instead of rows I want to display them in a column. So I am using pivot. But pivot uses aggregate method to bind, and I need two values from the table. So I made two functions. I can achieve the same by using switch case statement. Please suggest which one is best or have any other way to do it.

Here is my code

My first function

SELECT DISTINCT
        [1] AS OrderSubscriptionCustomFieldName1,
        [2] AS OrderSubscriptionCustomFieldName2,
        [3] AS OrderSubscriptionCustomFieldName3,
        [4] AS OrderSubscriptionCustomFieldName4,
        [5] AS OrderSubscriptionCustomFieldName5,
        [6] AS OrderSubscriptionCustomFieldName6,
        [7] AS OrderSubscriptionCustomFieldName7,
        [8] AS OrderSubscriptionCustomFieldName8,
        [9] AS OrderSubscriptionCustomFieldName9,
        [10] AS OrderSubscriptionCustomFieldName10,
        [11] AS OrderSubscriptionCustomFieldName11,
        [12] AS OrderSubscriptionCustomFieldName12,
        [13] AS OrderSubscriptionCustomFieldName13,
        [14] AS OrderSubscriptionCustomFieldName14,
        [15] AS OrderSubscriptionCustomFieldName15
    FROM
        (SELECT cfeCustomFieldID, nmfDisplayName FROM NamespaceCustomField WHERE nmmNamespaceMasterID = @p_NamespaceMasterID ) AS PivotCustomField

        PIVOT (max (nmfDisplayName)
        FOR cfeCustomFieldID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])) AS PivotedTable

我的第二个功能

My second function

 SELECT DISTINCT
 [1] AS NamespaceCustomFieldID1,
 [2] AS NamespaceCustomFieldID2,
 [3] AS NamespaceCustomFieldID3,
 [4] AS NamespaceCustomFieldID4,
 [5] AS NamespaceCustomFieldID5,
........... till last 15 column
 [15] AS NamespaceCustomFieldID15

FROM
 (SELECT cfeCustomFieldID, nmfNamespaceCustomFieldID FROM NamespaceCustomField WHERE nmmNamespaceMasterID = @p_NamespaceMasterID ) AS PivotCustomField

 PIVOT (MAX (nmfNamespaceCustomFieldID)
 FOR cfeCustomFieldID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])) AS PivotedTable

使用Case语句也一样

The same by using Case Statement

SELECT
    nmmNamespaceMasterID,
    MIN(CASE WHEN cfeCustomFieldID = 1 THEN nmfNamespaceCustomFieldID ELSE NULL END) NamespaceCustomFieldID1,
    MIN(CASE WHEN cfeCustomFieldID = 1 THEN nmfDisplayName ELSE NULL END) OrderSubscriptionCustomFieldName1,
    MIN(CASE WHEN cfeCustomFieldID = 2 THEN nmfNamespaceCustomFieldID ELSE NULL END) NamespaceCustomFieldID2,
    MIN(CASE WHEN cfeCustomFieldID = 2 THEN nmfDisplayName ELSE NULL END) OrderSubscriptionCustomFieldName2,
    MIN(CASE WHEN cfeCustomFieldID = 3 THEN nmfNamespaceCustomFieldID ELSE NULL END) NamespaceCustomFieldID3,
    MIN(CASE WHEN cfeCustomFieldID = 3 THEN nmfDisplayName ELSE NULL END) OrderSubscriptionCustomFieldName3,
    MIN(CASE WHEN cfeCustomFieldID = 4 THEN nmfNamespaceCustomFieldID ELSE NULL END) NamespaceCustomFieldID4,
    MIN(CASE WHEN cfeCustomFieldID = 4 THEN nmfDisplayName ELSE NULL END) OrderSubscriptionCustomFieldName4,
    MIN(CASE WHEN cfeCustomFieldID = 5 THEN nmfNamespaceCustomFieldID ELSE NULL END) NamespaceCustomFieldID5,
  ..............  till last 

    MIN(CASE WHEN cfeCustomFieldID = 15 THEN nmfNamespaceCustomFieldID ELSE NULL END) NamespaceCustomFieldID15,
    MIN(CASE WHEN cfeCustomFieldID = 15 THEN nmfDisplayName ELSE NULL END) OrderSubscriptionCustomFieldName15
FROM
    NamespaceCustomField
GROUP BY
    nmmNamespaceMasterID

推荐答案

枢轴在内部像case语句一样构建，因此具有基本相同的性能.
但是，虽然透视图更易于编码，且打字和重复变量较少，因此容易产生错别字，但case语句版本更可自定义.
因此，这取决于您的需求.

The pivot is internally built like a case statement and is therefore having basically the same performance.
But while pivot is easier to code with less typing and repetition of variables and therefore prone to have less typos, the case statement version is more customisable.
So it''s up to what you need.

这篇关于使用数据透视表或选择大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！