SQL pivot with no aggregate

Question

UPDATE

I have modified the SQL Fiddle to make the department names more reflective of what I am trying to get to. If you look at the fiddle there are 3 select statements.

1. Lists the departments in the correct order from the data
2. Result from my attempt at a pivot
3. Fake select so show what I am TRYING to get to

In the result of my pivot attempt the second row is displaying a null for departmentLevel1 and departmentLevel3 should NOT be in this row. I have tried various combination of row_number and partitions but I can't seem to figure this out. Totally frustrated!

Here is my latest fiddle http://sqlfiddle.com/#!3/175af/40

BEFORE YOU VOTE TO CLOSE Please read this question and then read the question sited as a duplicate. The question sited is MUCH more simplistic. Not only is my question asking about pivoting but grouping the pivot results and doing much more that pivoting start and stop times. I think are only looking at the title. If you don't want to read the whole question that is ok but don't assume it is the same as some other question with a similar title please.

I realize that I must have an aggregate so I tried using max but I am really just trying to display this info in more tabular way so I can join this to another data set.

Here is the starting data:

select  departmentID, departmentParentID, departmentName, departmentGroupSortBy, departmentLevel, sortBy
from    vDepartmentList
where   regionID = 3
order by departmentGroupSortBy, departmentLevel, sortBy

departmentID    departmentParentID  departmentName  departmentGroupSortBy   departmentLevel sortBy
---------------------------------------------------------------------------------------------------
111                                 neth test dept  1                       1                1
115             111                 test sub1       1                       2               1
112                                 test2           2                       1                2
113             112                 new sub2        2                       2               1
114             112                 new sub 3a      2                       2                 2
116             114                 should L3       2                       3                 1

If I run this query:

select  departmentParentID, [1] as departmentLevel1, [2] as departmentLevel2, [3] as departmentLevel3
from    (
    select  departmentParentID, departmentLevel, departmentName
    from    vDepartmentList
    where   regionID = 3
    ) p
pivot (
    max(departmentName)
    for departmentLevel in ([1],[2],[3])
    ) as  pvt
order by departmentParentID

I get this result :

departmentParentID  departmentLevel1    departmentLevel2    departmentLevel3
----------------------------------------------------------------------------
                    test2       
111                                     test sub1   
112                                     new sub2    
114                                                          should L3

But What I am trying to get is :

departmentParentID  departmentLevel1    departmentLevel2    departmentLevel3
----------------------------------------------------------------------------
111                 neth test dept      test sub1   
112                 test2               new sub2    
112                 test2               new sub 3a          should L3

Does anyone have a suggestion as to where I have gone wrong?

Answer 1

Since you need to return 3 levels of a hierarchy data structure it might be significantly easier to use JOINs instead of using a PIVOT. You would start by returning the parent rows:

select p.departmentName
from dbo.department p
where p.departmentParentID is null;

This will get you all of the top level rows. Then you start adding a join for each additional level that you need, in your case you need a total of three levels so you will add two joins. The final query will be the following:

select p.departmentName department1,
    c.departmentName department2,
    gc.departmentName department3
from dbo.department p         -- parent level
left join dbo.department c    -- child level 
    on p.departmentID = c.departmentParentID
left join dbo.department gc   -- grandchild level
    on c.departmentID = gc.departmentParentID
where p.departmentParentID is null;

See SQL Fiddle with Demo.

It seems that this will be easier to get the result using a JOIN instead of using the PIVOT. This uses the source data not the recursive view that you created. This gives a result of:

| DEPARTMENT1 | DEPARTMENT2 |  DEPARTMENT3 |
|-------------|-------------|--------------|
|     depart1 |     d1 sub1 |       (null) |
|     depart2 |      d2 sub |       (null) |
|     depart2 |     d2 sub2 | d2s2 subSub1 |

If you want to use a recursive query to get the result, then you could alter your current view slightly to return the 3 columns of names instead of the level numbers:

;with cte as
(
    select departmentID, 
        departmentParentID,  
        departmentName as Department1,
        cast(null as varchar(100)) Department2,
        cast(null as varchar(100)) Department3,
        1 as Level
    from dbo.department
    where departmentParentID is null
    union all
    select d.departmentID, 
        d.departmentParentID,
        Department1,
        case when Level + 1 = 2 then d.departmentName else Department2 end,
        case when Level + 1 = 3 then d.departmentName else Department3 end,
        Level + 1
    from dbo.department d
    inner join cte h
        on d.departmentParentID = h.departmentID
)
select *
from cte;

See SQL Fiddle with Demo. You could then do some filtering using a WHERE clause to return the rows that have all of the department values that you need.

Answer 2

i created below sql based on your view sql. you almost get the result in there.

i use @temp table to remove the order by clause. probably you can remove the @temp table in the implementation.

please try below sql:

declare @temp table
(
  id int identity,
  departmentLevel1 int,
  departmentLevel2 int,
  departmentLevel3 int,
  xRow int
)

insert into @temp 
select 
    d.lvl1, d.lvl2, d.lvl3
    ,Rrow
from
(
select 
    a.lvl1, a.lvl2, a.lvl3
    ,row_number() over(partition by a.lvl1,a.lvl2 order by a.lvl1,a.lvl2) as Rrow
    ,departmentGroupSortby, departmentlevel, sortby
from 
    vDepartmentList a
) d
where (d.lvl2 is not null) 
order by departmentGroupSortby, departmentlevel, sortby
;

select
    c.departmentLevel1 as departmentParentID
    ,Case
        when c.departmentLevel1 = d.departmentid then d.DepartmentName
        else 
            null
     end as DepartmentLevel1
    ,Case
        when c.departmentLevel2 = e.departmentid then e.DepartmentName
        else 
            null
     end as DepartmentLevel2
    ,Case
        when c.departmentLevel3 = f.departmentid then f.DepartmentName
        else 
            null
     end as DepartmentLevel3
    --,d.departmentId   ,e.departmentid ,f.departmentid
from
(
select 
    a.id, a.departmentLevel1,a.departmentLevel2,a.departmentLevel3
    ,a.xRow
from @temp a
inner join
(
select 
    cast(departmentLevel1 as nvarchar(5)) + cast(departmentLevel2 as nvarchar(5)) as xrow, count(cast(departmentLevel1 as nvarchar(5)) + cast(departmentLevel2 as nvarchar(5))) as xcount
from @temp
group by cast(departmentLevel1 as nvarchar(5)) + cast(departmentLevel2 as nvarchar(5))
) b on cast(a.departmentLevel1 as nvarchar(5)) + cast(a.departmentLevel2 as nvarchar(5)) = b.xrow and a.xRow = b.xcount
) c
left join department d on c.departmentLevel1 = d.departmentId
left join department e on c.departmentLevel2 = e.departmentid
left join department f on c.departmentLevel3 = f.departmentid

Result

departmentParentID DepartmentLevel1 DepartmentLevel2 DepartmentLevel3
------------------ ---------------- ---------------- ----------------
111                depart1          d1 sub1          NULL
112                depart2          d2 sub           NULL
112                depart2          d2 sub2          d2s2 subSub1

Hope this is help

Answer 3

Pivoting is like grouping in a way. You need a column in the original source to group by. In this case you dont have it, and pivot hes no idea that 'test sub1' is in any way connected to 'neth test dept'. What we need here is to establish the root for the hierarchy. We can do it with a small recursive CTE (replace #t with vDepartmentList):

;with x as (
    select *, departmentID as root
    from #t
    where departmentParentID is null
    union all
    select #t.*, x.root
    from #t
    inner join x on #t.departmentParentID = x.departmentID
)
select * 
from x

So now we have a root and also anther problem as well: we have only two roots, but you want to have three rows in the result. Pivot operater will essentially group on root column and will treat 'new sub2', 'new sub 3a' and 'should L3' as all belonging to 'test2', and produce a single row with respective max of these values.

There should be a way to differentiate, but I can't see it in your data. So, as it seems, you're out of luck.