SQL 中的数据透视/反透视

发布于 2024-08-07 19:03:59 字数 344 浏览 11 评论 0原文

我在 SQL 中有一个视图，它是通过分析表中的值生成的，以便该字段包含值“N”、“D”或“V”。我可以按列计算总计，但不能按行计算总计......这可能吗？

示例：

数据

No, Col_1, Col_2, Col_3

 1,     N,     N,     N

 2,     N,     D,     D

 3,     N,     V,     D

 4,     V,     V,     V

如何总结第 3 行有 1N、1V 和 3ds，而第 4 行有 4V？

打赌很简单，但可悲的是我也是！

预先非常感谢，彼得

原文

I have a view in SQL that I have generated by analysing the values in tables so that field either contain the value 'N', 'D' or 'V'. I can work out the totals by column but not by row... Is this possible?

Example:

Data

No, Col_1, Col_2, Col_3

 1,     N,     N,     N

 2,     N,     D,     D

 3,     N,     V,     D

 4,     V,     V,     V

How do I summise that Row 3 has 1N, 1V and 3ds whilst Row 4 has 4Vs?

Bet is quite simple but sadly so am I!

Many thanks in advance,
Peter

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

轻拂→两袖风尘 2024-08-14 19:03:59

 select case when col_1 = 'N' then 1 else 0 end as n_count from tablename;

概括地说：

 select 
   case when col_1 = 'N' then 1 else 0 end 
   + case when col_2 = 'N' then 1 else 0 end 
   + case when col_2 = 'N' then 1 else 0 end as n_count,
   case when col_1 = 'V' then 1 else 0 end 
   + case when col_2 = 'V' then 1 else 0 end 
   + case when col_2 = 'V' then 1 else 0 end as v_count,
   ....
  from tablename;

 select case when col_1 = 'N' then 1 else 0 end as n_count from tablename;

Generalizing that:

 select 
   case when col_1 = 'N' then 1 else 0 end 
   + case when col_2 = 'N' then 1 else 0 end 
   + case when col_2 = 'N' then 1 else 0 end as n_count,
   case when col_1 = 'V' then 1 else 0 end 
   + case when col_2 = 'V' then 1 else 0 end 
   + case when col_2 = 'V' then 1 else 0 end as v_count,
   ....
  from tablename;

回复收藏 0 原文

囍笑 2024-08-14 19:03:59

怎么样？

select no,
sum(case when val = 'N' then 1 else 0 end) ncnt,
sum(case when val = 'V' then 1 else 0 end) vcnt,
sum(case when val = 'D' then 1 else 0 end) dcnt from
(select no, col_1 val from t union all 
 select no, col_2 from t union all
 select no, col_3 from t)
group by no
order by no

How about?

select no,
sum(case when val = 'N' then 1 else 0 end) ncnt,
sum(case when val = 'V' then 1 else 0 end) vcnt,
sum(case when val = 'D' then 1 else 0 end) dcnt from
(select no, col_1 val from t union all 
 select no, col_2 from t union all
 select no, col_3 from t)
group by no
order by no

回复收藏 0 原文

~没有更多了~