基于单一源数据的多重透视

发布于 2024-09-02 05:14:11 字数 987 浏览 2 评论 0原文

我正在尝试对源数据进行多点透视（如下所示）

alt text http://img532.imageshack .us/img532/5418/sourcex.jpg

希望结果为单行（如下所示）

alt text http://img35.imageshack.us/img35/1517/expected.jpg

到目前为止我的查询是

SELECT  *
FROM    ( SELECT    *
      FROM      ( SELECT    NK,
                            DC,
                            VERSION,
                            GEV
                  FROM      MULTIPIVOT
                ) SRC PIVOT ( MAX(GEV) FOR DC IN ( [10], [11], [12], [18] ) ) AS PVT
    ) SRC PIVOT ( MAX([18]) FOR VERSION IN ( [2006], [2007], [2008],[2009] ) )AS PVT

将结果输出为

替代文本 http://img22.imageshack.us/img22/8703/resultos.jpg

将其作为单行的方法是什么？

谢谢

原文

I am trying to mutlipivot source data (as below )

alt text http://img532.imageshack.us/img532/5418/sourcex.jpg

want results as single row (as below)

alt text http://img35.imageshack.us/img35/1517/expected.jpg

My query so far is

SELECT  *
FROM    ( SELECT    *
      FROM      ( SELECT    NK,
                            DC,
                            VERSION,
                            GEV
                  FROM      MULTIPIVOT
                ) SRC PIVOT ( MAX(GEV) FOR DC IN ( [10], [11], [12], [18] ) ) AS PVT
    ) SRC PIVOT ( MAX([18]) FOR VERSION IN ( [2006], [2007], [2008],[2009] ) )AS PVT

which outputs results as

alt text http://img22.imageshack.us/img22/8703/resultos.jpg

what is the way to get this as single row?

Thanks

分享到QQ

分享到微博

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

发布评论

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

香草可樂 2024-09-09 05:14:11

我认为，如果您有两种截然不同类型的值（年份和版本号）存储在同一列中，并且某些内容应该和不应该出现在给定列中的逻辑是，那么数据库设计中存在缺陷有点模糊，但是，假设奇数存储是一致的，有一种方法可以在单行中获得您想要的结果。

Select NK
    , Min( Case When DC = 10 Then GEV End ) As [10]
    , Min( Case When DC = 11 Then GEV End ) As [11]
    , Min( Case When DC = 12 Then GEV End ) As [12]
    , Min( Case When DC = 18 And Version = 2006 Then GEV End ) As [2006]
    , Min( Case When DC = 18 And Version = 2007 Then GEV End ) As [2007]
    , Min( Case When DC = 18 And Version = 2008 Then GEV End ) As [2008]
    , Min( Case When DC = 18 And Version = 2009 Then GEV End ) As [2009]
From Multipivot
Group By NK

I would argue that there is a flaw in the database design if you have two very different types of values (a year and version number) stored in the same column and the logic by which something should and should not appear in a given column is a little hazy, however, there is a means to get the result you want in a single row presuming that the odd storage is consistent.

Select NK
    , Min( Case When DC = 10 Then GEV End ) As [10]
    , Min( Case When DC = 11 Then GEV End ) As [11]
    , Min( Case When DC = 12 Then GEV End ) As [12]
    , Min( Case When DC = 18 And Version = 2006 Then GEV End ) As [2006]
    , Min( Case When DC = 18 And Version = 2007 Then GEV End ) As [2007]
    , Min( Case When DC = 18 And Version = 2008 Then GEV End ) As [2008]
    , Min( Case When DC = 18 And Version = 2009 Then GEV End ) As [2009]
From Multipivot
Group By NK

回复收藏 0 原文

~没有更多了~