CODEWITHSUNDEEP
c#sqljoinstored-procedurespivot
L. Stotch
L. Stotch

Reputation: 35

Joining across several tables - then applying a PIVOT to display the data differently

I am trying to figure a way to execute a seemingly complex join scenario, and am unsure as to how to go about it. Some Background info:

-I have a 'ProjectCategory' table, which contains a foreign key 'ProjectID' and 'CategoryID' to the Project and Category tables respectively. One project could have as many assigned categories to it as there are existing (up to 10)

-I have a 'Budget' table and a 'Sponsor' Table.

-My 'Project' table is related to my 'Budget' Table in that all Projects have an associated BudgetID

-My 'Budget' Table is related to my 'Sponser' table in that all Budgets have an associated SponsorID.

-'Project' table and 'Sponsor' table are not directly related.

An example of the result set that I am trying to get is firstly:

SponsorName(Field in sponsor table) - ProjectName - Category
___________________________________   ___________   ________
A                                     ABC           categoryA
A                                     ABC           categoryB
A                                     DEF           categoryX
A                                     DEF           categoryZ

I would then like to use a PIVOT to show the data like:

SponsorName  - ProjectName -categoryA - categoryB -categoryC - categoryD ...
___________    ___________  _________   _________  _________   _________
A              ABC             X            X
A              DEF                          X                      X
B              EFG             X                                   X

Where the Xs mark which categories are associated with each project/sponsor combination. The filling in of the Xs is maybe something I will do in the codebehind or using other stored procedures, but this is the basic idea of what I am trying to do.

I am having trouble even figuring out how to write a query to get back the first set before I even implement a pivot to show it as the second set, so I am a bit intimidated by this task. Any help greatly appreciated, and please let me know if you need any more information

Upvotes: 0

Views: 39

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Assuming SQL Server, I use a stored procedure for the bulk of Dynamic PIVOTS. (Listed Below)

The source could be a table, #temp or even SQL

Exec [prc-Pivot] '#Temp','Category','max(''X'')[]','SponsorName,ProjectName',null

Returns

SponsorName ProjectName categoryA   categoryB   categoryD   categoryX   categoryZ
A           ABC         X           X           NULL        NULL        NULL
A           DEF         NULL        NULL        NULL        X           X
B           EFG         X           NULL        X           NULL        NULL

The Stored Procedure

CREATE PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'


Set NoCount On
Set Ansi_Warnings Off

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),NULL) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)


Set NoCount Off
Set Ansi_Warnings on

Upvotes: 1

Related Questions