Multi Pivoting on single Source data

Question

I am trying to mutlipivot source data (as below )

want results as single row (as below)

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

what is the way to get this as single row?

Thanks

Answer 1

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