Pivot on three different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot on three different tables

Author

Topic

Sadhu
Starting Member

14 Posts

Posted - 2014-04-03 : 07:20:24

I have three tables as given below :

AssociateTable :

AssoID | AssociateName
1 | Asso1
2 | Asso2
3 | Asso3

ActivityTable :

ActivityID | ActivityName
1 | Act1
2 | Act2
3 | Act3
4 | Act4

AssociateActivityMappingTable :

AssoID | ActivityID
1 | 2
1 | 3
2 | 2

I want the result as given below :

AssociateName | Act1 | Act2 | Act3 | Act4

Asso1 | N | Y | Y | N
Asso2 | N | Y | N | N
Asso2 | N | N | N | N

Any help on this is much appreciated.

Thanks in Advance!
Sadhu.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 08:03:16

[code]
;with AssociateTable
AS
(select 1 as AssoID, 'Asso1' as AssociateName union all
select 2, 'Asso2' union all
select 3, 'Asso3')
,ActivityTable
AS
(select 1 ActivityID, 'Act1' as ActivityName union all
select 2 , 'Act2' union all
select 3 , 'Act3' union all
select 4 , 'Act4')
,
AssociateActivityMappingTable
AS
(select 1 as AssoID, 2 as ActivityID union all
select 1 , 3 union all
select 2 , 2)
,aCTE
AS
(
SELECT
CASE WHEN AAMT.AssoID IS NOT NULL THEN 'Y'
ELSE 'N' end as AssoID
,AssociateName
,ActivityName
FROM
AssociateTable AsT
CROSS JOIN ActivityTable AcT

FULL JOIN AssociateActivityMappingTable AAMT
ON AAMT.AssoID=AsT.AssoID
AND AAMT.ActivityID=AcT.ActivityID)

SELECT
*
FROM aCTE

PIVOT
(min(AssoID) FOR [ActivityName] IN ([Act1],[Act2],[Act3],[Act4])) X

[/code]

sabinWeb MCP

Sadhu
Starting Member

14 Posts

Posted - 2014-04-03 : 09:44:46

That worked! Thank you very much!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 09:51:13

You are welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources