Using union to create a derived table

building semantic layer
1

Hi

I have a table made this way:

Matter_ID    Name      Type
1090           ABC         primary business unit
1090           XYZ         participating business unit

I want it to be like this to serve my reporting:

Matter_ID    Primary_B_U      Participating_B_U
1090           ABC                   XYZ

So, I thaught of using a union to make this work

Here is the exact code that I have created:


select
dbo.MatterPlayer.MatterNumber_ID,
dbo.Entity.Name as Participating_Business_Unit,
null as Primary_Business_Unit

From dbo.Entity
INNER JOIN dbo.MatterPlayer ON ( dbo.Entity.Entity_EID = dbo.MatterPlayer.Entity_EID AND dbo.MatterPlayer.Role_CD = 'Participating Business Unit' ) 

union

select  
dbo.MatterPlayer.MatterNumber_ID,
null as Participating_Business_Unit,
dbo.Entity.Name as Primary_Business_Unit

From dbo.Entity
INNER JOIN dbo.MatterPlayer ON ( dbo.Entity.Entity_EID = dbo.MatterPlayer.Entity_EID AND dbo.MatterPlayer.Role_CD = 'Primary Business Unit' )

I dont understant why my results are like this:


Matter_ID    Primary_B_U      Participating_B_U
1090                                    XYZ
1090            ABC

can you please tell me if I m wrong with some points

Regards,

zizou :tunisia: (BOB member since 2008-07-31)

2

Quick question…do you have a need to do this at the universe level ?

Have you considered doing this at the report level, using 2 data proiders and merging the 2 on Matter ID ?

BTW…the union is doing what it is supposed to do, eleminating duplicates and in your case there are not really any duplicate rows.

What you need is a join and not union…
If you want to stick to derived tables try creating 2 derived tables (one for participating Bu and the other for primary BU) and joining the 2.

Regards,

Naresh Ganatra :us: (BOB member since 2004-04-09)

3

Hello

Your question is excellent! The situation now is like that. My clients have to do two queries everytime the want to make report as simple as this:

Section = primary business unit

Bloc: Matter, Participating business unit.

Now my clients are unhappy(very!). I understand that it should be done at the universe level (as it is not done at the data base level).

Regards,

zizou :tunisia: (BOB member since 2008-07-31)

4

You need to pivot the data, the general idea would be as follows


select MatterNumber_ID,
         max(case when Role_cd='Participating Business Unit' then Entity.Name else null end),
         max(case when Role_cd = 'Primary Business Unit' then Entity.Name else null end)
from <your tables, joins, etc.>
group by MatterNumber_ID

jwhite9 :us: (BOB member since 2006-07-28)

5

Juste tried it and it worked veeeeeeeeeeeeery Fine! thank you for your idea

Regards,

zizou :tunisia: (BOB member since 2008-07-31)