代码之家 › 专栏 › 技术社区 › Leslie

SQL数据透视不太正确

pivot sql-server-2008 sql

Leslie · 技术社区 · 6 年前

也许我只是不够清醒……我知道我过去做过类似的事情,当我看到这里的其他答案时,我认为我在做同样的事情,但我没有得到预期的结果。

我有个问题:

选择
tpm.mast_rel作为mast_rel
,行_number()over(按tpm.mast_rel分区,按tpm.mast_rel排序)作为CategoryCount
,S.[rc_trans]作为[类别]
,SUM(P.[VAL])作为[值]
来自案例列表作为TPM
内部连接[Tiburon]。[ParsProperty]作为TPM上的P。[Mast_Rel]=P.[Mast_Rel]
--当p.cat='y'时,s.rc_key等于p.cat-p.art的组合,否则仅p.cat=rc_key
左连接[Tiburon]。[SSCTAB]为打开状态(大小写)
当p.[cat]='y'时,则p.[cat]+'-'+p.[art]
否则P.[猫]
end)=s.[rc_key]和s.[rc_type]='cp'
在那里,P.[P invl]!='evd'和s.[rc_trans]不为空
按tpm.mast_rel,s.【rc_trans】分组
< /代码> 

这给了我这些结果:


我想以轴为轴,这样我就可以得到一个带有三列类别的桅杆。

select mast_rel,[1],[2],[3]
从
(

选择
tpm.mast_rel作为mast_rel
,行_number()over(按tpm.mast_rel分区,按tpm.mast_rel排序)作为CategoryCount
,S.[rc_trans]作为[类别]
,SUM(P.[VAL])作为[值]
来自案例列表作为TPM
内部连接[Tiburon]。[ParsProperty]作为TPM上的P。[Mast_Rel]=P.[Mast_Rel]
--当p.cat='y'时,s.rc_key等于p.cat-p.art的组合,否则仅p.cat=rc_key
左连接[Tiburon]。[SSCTAB]为打开状态(大小写)
当p.[cat]='y'时,则p.[cat]+'-'+p.[art]
否则P.[猫]
end)=s.[rc_key]和s.[rc_type]='cp'
在那里,P.[P invl]!='evd'和s.[rc_trans]不为空
按tpm.mast_rel,s.【rc_trans】分组
)
SRC
枢轴
(
CategoryCount在([1]、[2]、[3]中的最大值(类别)
PIV

订单1;
< /代码> 

但是,我没有得到一行,而是把每一行都放在自己的行上:



此外,我还需要在透视图上为值列设置一个“总计”。所以最后我想要一张唱片,上面写着:



有人能帮我调整我的查询以得到我需要的结果吗?
谢谢您!

<编辑>:
下面是一个将创建数据和当前结果的脚本:

declare@results table(mast_rel varchar(100),categorycount varchar(10),category varchar(100),[value]varchar(100))

插入@results(mast_rel,categorycount,category,[value])
价值观

('1602030055590P2404'、'1'、'money'、'80.00'),
('1602051033480P3481'、'1'、'其他/其他(以上无)'、'1000.00',
('1602051033480P3481'、'2'、'Personal Accessories(including serial jewellery)'、'5000.00',
('1602051033480P3481'、'3'、'Radio,TV,and Sound Entertainment Devices'、'')
('1602070005106P2804'、'1'、'Miscellaneous/Other(none of the above)'、'),
('1602080020374P3352'、'1'、'money'、'128.09'),
('1602080020374P3352'、'2'、'收音机、电视和音响娱乐设备'、'')
('160213249110P5208'、'1'、'money'、'160.00'),
('160213249110P5208'、'2'、'Radio,TV,and Sound Entertainment Devices'、''),
('1602171004296P3848'、'1'、'Consumable Goods'、'21.73'),
('1602201425504P2876'、'1'、'Radio,TV,and Sound Entertainment Devices'、'')
('16022115223610P3282'、'1'、'Consumable Goods'、'60.00'),
('16022115223610p3282'、'2'、'money'、'300.00'),
('16022115223610p3282'、'3'、'麻醉设备/用具'、'10.00'),
('1602250140284P2804'、'1'、'money'、'165.00'),
('1602250140284P2804'、'2'、'Radio,TV,and Sound Entertainment Devices'、''),
('16022916203812P2702'、'1'、'Guns/Firearms'、'')
('16022916203812P2702'、'2'、'收音机、电视和音响娱乐设备'、'')


选择mast_rel[1]、[2]、[3]
从
(

选择
*从@结果
)
SRC
枢轴
(
CategoryCount在([1]、[2]、[3]中的最大值(类别)
PIV

订单1;
< /代码> <但是我没有得到预期的结果。


我有这个问题:


SELECT 
      tPM.mast_rel as Mast_Rel
    , row_Number() over(Partition by tPM.Mast_rel Order by tPM.Mast_rel) as CategoryCount
    ,  S.[RC_TRANS] as [Category]
    ,  SUM(P.[VAL]) as [Value]
FROM #caselist AS tPM
INNER JOIN [TIBURON].[PARSProperty] AS P ON tPM.[MAST_REL] = P.[MAST_REL]
--S.RC_KEY equals combination of P.CAT-P.ART when P.CAT ='Y' otherwise just P.CAT = RC_KEY
LEFT JOIN [TIBURON].[SSCTAB] AS S ON (CASE
        WHEN P.[CAT] = 'Y' THEN P.[CAT] + '-' + P.[ART]
        ELSE P.[CAT]
    END) = S.[RC_KEY] AND S.[RC_TYPE] = 'CP'
WHERE P.[P_INVL] != 'EVD' and S.[RC_TRANS] is not null
GROUP BY tPM.mast_rel, S.[RC_TRANS] 


这给了我这些结果:


我想以轴为轴,这样我就可以得到一个带有三列类别的桅杆。


select Mast_Rel,[1], [2], [3]
from
(

SELECT 
      tPM.mast_rel as Mast_Rel
    , row_Number() over(Partition by tPM.Mast_rel Order by tPM.Mast_rel) as CategoryCount
    ,  S.[RC_TRANS] as [Category]
    ,  SUM(P.[VAL]) as [Value]
FROM #caselist AS tPM
INNER JOIN [TIBURON].[PARSProperty] AS P ON tPM.[MAST_REL] = P.[MAST_REL]
--S.RC_KEY equals combination of P.CAT-P.ART when P.CAT ='Y' otherwise just P.CAT = RC_KEY
LEFT JOIN [TIBURON].[SSCTAB] AS S ON (CASE
        WHEN P.[CAT] = 'Y' THEN P.[CAT] + '-' + P.[ART]
        ELSE P.[CAT]
    END) = S.[RC_KEY] AND S.[RC_TYPE] = 'CP'
WHERE P.[P_INVL] != 'EVD' and S.[RC_TRANS] is not null
GROUP BY tPM.mast_rel, S.[RC_TRANS] 
)
src
pivot
(
max(Category) for CategoryCount in ([1], [2], [3])
) piv

order by 1;


但是,我不是得到一行,而是得到每一行:




此外,我还需要在透视图上为值列设置一个“总计”。因此,最终我想要一个单一的记录,显示:




有人能帮我调整我的查询以得到我需要的结果吗?
谢谢您!


编辑:
下面是一个将创建数据和当前结果的脚本:


declare  @results table (Mast_Rel varchar(100), CategoryCount varchar(10), Category varchar(100) , [Value] varchar(100))

insert into @results (Mast_rel, CategoryCount, Category, [Value]) 
values

 ('1602030055590P2404','1','Money','80.00'),
('1602051033480P3481','1','Miscellaneous/other (None of the above)','1000.00'),
('1602051033480P3481','2','Personal accessories (incl serial jewelry)','5000.00'),
('1602051033480P3481','3','Radio, TV, and sound entertainment devices',''),
('1602070005106P2804','1','Miscellaneous/other (None of the above)',''),
('1602080020374P3352','1','Money','128.09'),
('1602080020374P3352','2','Radio, TV, and sound entertainment devices',''),
('1602132349110P5208','1','Money','160.00'),
('1602132349110P5208','2','Radio, TV, and sound entertainment devices',''),
('1602171004296P3848','1','Consumable Goods','21.73'),
('1602201425504P2876','1','Radio, TV, and sound entertainment devices',''),
('16022115223610P3282','1','Consumable Goods','60.00'),
('16022115223610P3282','2','Money','300.00'),
('16022115223610P3282','3','Narcotic Equipment/Paraphernalia','10.00'),
('1602250140284P2804','1','Money','165.00'),
('1602250140284P2804','2','Radio, TV, and sound entertainment devices',''),
('16022916203812P2702','1','Guns/Firearms',''),
('16022916203812P2702','2','Radio, TV, and sound entertainment devices','')


select Mast_Rel,[1], [2], [3]
from
(

SELECT 
     * from @results
)
src
pivot
(
max(Category) for CategoryCount in ([1], [2], [3])
) piv

order by 1;

4 回复 | 直到 6 年前

Taryn Frank Pearson 6 年前

您应该能够更改原始查询以获得所需的结果。问题在于 GROUP BY 以及 SUM() 在里面。因为你在分组 S.[RC_TRANS] 对于 () 您将返回多行,这将更改 PIVOT .

你可以移除 小组通过 在内部子查询和使用中 SUM() OVER() 相反。将原始查询更改为以下值将得到所需的结果:

select Mast_Rel,[1], [2], [3], [Value]
from
(
  SELECT 
      tPM.mast_rel as Mast_Rel
    , row_Number() over(Partition by tPM.Mast_rel Order by tPM.Mast_rel) as CategoryCount
    ,  S.[RC_TRANS] as [Category]
    -- change the following line
    ,  SUM(P.[VAL]) OVER(PARTITION BY tPM.Mast_rel)  as [Value]
  FROM #caselist AS tPM
  INNER JOIN [TIBURON].[PARSProperty] AS P ON tPM.[MAST_REL] = P.[MAST_REL]
  --S.RC_KEY equals combination of P.CAT-P.ART when P.CAT ='Y' otherwise just P.CAT = RC_KEY
  LEFT JOIN [TIBURON].[SSCTAB] AS S ON (CASE
          WHEN P.[CAT] = 'Y' THEN P.[CAT] + '-' + P.[ART]
          ELSE P.[CAT]
      END) = S.[RC_KEY] AND S.[RC_TYPE] = 'CP'
  WHERE P.[P_INVL] != 'EVD' and S.[RC_TRANS] is not null
)
src
pivot
(
  max(Category) for CategoryCount in ([1], [2], [3])
) piv
order by 1;

从 SUM(P.[VAL]) 用一个 小组通过 到 SUM(P.[VAL]) OVER(PARTITION BY tPM.Mast_rel) 你得到的是每个人的总数 tPM.Mast_rel 这就是你想要在最终结果集中返回的结果。这个 SUM(P.[VAL]) OVER 应为中的每一行计算相同的值 Mast_Rel 这样就不会在最终结果集中创建多行。

Yogesh Sharma 6 年前

我将进行条件聚合:

select mast_rel, 
       max(case when categorycount = 1 then category end), 
       max(case when categorycount = 2 then category end), 
       max(case when categorycount = 3 then category end),
       sum(value)
from @results r
group by mast_rel;

-1

incomudro 6 年前

不确定,因为我不能直接测试它。但是当您向PIVOT子句添加max(mast_rel)时会发生什么?

pivot (
    max(Mast_Rel), max(Category) for CategoryCount in ([1], [2], [3])
) piv

-1

Eric 6 年前

你可以再把它包起来 GROUP BY

SELECT Mast_Rel, MAX([1]) AS [1], MAX([2]) AS [2], MAX([3]) AS [3]
FROM
(
    SELECT * 
    FROM @results
) src
PIVOT
(
    MAX(Category) FOR CategoryCount IN ([1], [2], [3])
) piv
GROUP BY Mast_Rel
ORDER BY Mast_Rel;