时间戳

首页 > 解决方案 > 如何使用同一列创建数据透视查询

sql - 如何使用同一列创建数据透视查询

问题描述

我有一个看起来像这样的表：

在此处输入图像描述

我希望使用这些数据创建一个数据透视表并让它看起来像这样在此处输入图像描述

请注意模型如何是值行 1 和修订值行 2，值是值行 3，或者换句话说，模型 = test_key_id 23，修订 = test_key_id 25 和值 = test_key_id 24 我能够想出这个查询：

BEGIN
        --this is a copy of the query above just for troubleshooting purposes
        with t1 as (
            SELECT test_recipe_id, LTRIM(RTRIM(value)) as model_num , row_number() over (order by id ) rn
            FROM [leakOpportunities].[dbo].[test_recipe_value] WHERE test_key_id in(23)
        ),
        t2 as (
            SELECT  test_recipe_id,LTRIM(RTRIM(value)) as value, row_number() over (order by id ) rn
            FROM [leakOpportunities].[dbo].[test_recipe_value] WHERE test_key_id in(24)
        ),
        t3 as (
            SELECT  test_recipe_id,LTRIM(RTRIM(value)) as revision, row_number() over (order by id ) rn
            FROM [leakOpportunities].[dbo].[test_recipe_value] WHERE test_key_id in(25)
        ),
        t4 as (
            SELECT  id,LTRIM(RTRIM(value)) as autobrazeError1, row_number() over (order by id ) rn
            FROM [leakOpportunities].[dbo].[test_recipe_value] WHERE test_key_id in(1)
        ),
        t5 as (
            SELECT  id,LTRIM(RTRIM(value)) as autobrazeError2, row_number() over (order by id ) rn
            FROM [leakOpportunities].[dbo].[test_recipe_value] WHERE test_key_id in(2)
        )

        SELECT  model_num,revision,value, value-(CAST((value) AS INT) - CAST((autobrazeError1) AS INT)-CAST((autobrazeError2) AS INT)),CAST((value) AS INT) - CAST((autobrazeError1) AS INT)-CAST((autobrazeError2) AS INT)
        FROM t1 full outer join t2 on t1.rn = t2.rn
        full outer join t3 on t3.rn = t1.rn
        full outer join t4 on t4.rn = t1.rn
        full outer join t5 on t5.rn = t1.rn
        PRINT 'No Records inserted as the insert record was set to false' 
    END

请注意，在我的查询中，我做了一些额外的数学运算并添加了一些额外的东西。此查询不起作用，因为输入数据的方式并非所有记录都具有所有值，因此“rn”不是比较它们的安全方法。对于他们中的大多数人来说，这种方法有效，但存在一些差异。我还尝试使用“test_recipe_id”列作为匹配项，但我也得到了一些数据差异。

有人可以帮助我或为我指出正确的方向，或者我可以如何做到这一点，或者可能是一个数据透视查询

标签： sqlsql-serverpivot

解决方案

您不需要单独的 CTE 来使用行号而不是 id 来获取值。

一个简单的 GROUP BY 就足够了，以后也不需要 JOIN。

请参阅下面的查询：

; with t1 as
(
      select 
        model_num=max(case when test_key_id =23 then LTRIM(RTRIM(value)) end),
        revision =max(case when test_key_id =25 then LTRIM(RTRIM(value)) end),
        [value]  =max(case when test_key_id =24 then LTRIM(RTRIM(value)) end),
        autobrazeError1= max(case when test_key_id =1 then LTRIM(RTRIM(value)) end),
        autobrazeError2= max(case when test_key_id =2 then LTRIM(RTRIM(value)) end)
       from [leakOpportunities].[dbo].[test_recipe_value] 
      group by test_recipe_id
)

select 
    model_num,
    revision,
    [value],
    [value]
        - ISNULL(CAST([value] AS INT),0) 
        - ISNULL(CAST(autobrazeError1 AS INT),0)
        - ISNULL(CAST(autobrazeError2 AS INT),0),
    ISNULL(CAST([value] AS INT),0)
        - ISNULL(CAST(autobrazeError1 AS INT),0)
        - ISNULL(CAST(autobrazeError2 AS INT),0)
from t1

推荐阅读