August 24, 2009 at 2:28 pm
I have a database with a project table and a contact table with a one to many relationship. I would like to create a view that shows the project once with all contacts listed horizontally. This is an easy crosstab function normally but in this case there can be multiple Project managers and multiple leads so I need to display the project once with the all contacts. I would appreciate any help I can get. Thank you.
August 24, 2009 at 2:51 pm
Hi Regina,
please take the time to read the article I referenced in my signature.
This article will guide you through the steps on how to post sample data. Based on your description it's hard to come up with any recommendation/solution...
August 24, 2009 at 3:00 pm
Thank you I will try to post according to the format suggested in the article.
August 24, 2009 at 4:08 pm
Regina, do you just require the names of the contacts, or do you need email address, phone numbers, etc?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 4:30 pm
Just the contacts. This is an example of what I have in the EventContact table
Project Number ContactName ContactType
1 Regina ProjMgr
1 Jim ProjMgr
1 Joan Lead
1 Phil Lead
I need a crosstab report that looks like this
Project Number ProjMgr ProjMgr Lead Lead
1 Regina Jim Joan Phil
This would be an easy crosstab if there was only one project manager and one lead person.
Thanks for help with this.
August 24, 2009 at 5:26 pm
Thanks for your help.
August 25, 2009 at 8:09 am
This was another fun problem, and a great morning warm-up. The trick here was to use ROW_NUMBER() to provide a sequence number so that you could have unique values to test for the CASE statements. I arbitrarily ordered by the contact name in deciding who was Lead1 and Lead2 or ProjMgr1 and ProjMgr2, but if you have a seniority flag of some kind you could use that just as well.
declare @sample table (ProjectNumber int, ContactName varchar(20), ContactType varchar(20))
insert into @sample
select 1, 'Regina', 'ProjMgr' union all
select 1, 'Jim', 'ProjMgr' union all
select 1, 'Joan', 'Lead' union all
select 1, 'Phil', 'Lead' union all
select 2, 'Warren', 'ProjMgr' union all
select 2, 'Jim', 'ProjMgr' union all
select 2, 'Joan', 'Lead' union all
select 2, 'Amanda', 'Lead' union all
select 2, 'Jeff','Lead'
-- select * from @sample
-------------------------------------------------------------------------------------------------
-- Everything above here is simply to create a sample data table.
-- The solution begins immediately following these comments.
-------------------------------------------------------------------------------------------------
declare @sql nvarchar(4000)
;with cte1 as
(select *,row_number() over(partition by projectNumber,ContactType order by ContactName) as seq from @sample)
select projectNumber,ContactName,ContactType+cast(seq as varchar(3)) as ColumnHdg
into #temp
from Cte1
-- select * from #temp
set @sql = 'select projectNumber '
;with cte1 as (select distinct columnHdg from #temp)
select @sql = @sql + ', max(case when columnHdg = '''+columnHdg+''' then contactName else '''' end) as ['+columnHdg+']'
from cte1
set @sql = @sql + ' from #temp group by projectNumber order by projectNumber'
print @sql
exec sp_executeSql @sql
drop table #temp
Edited to add comments to code.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 8:20 am
Thank you so much, this is way above my head but I will copy your example and work with it. When I see stuff like this I realize I have a lot to learn.
August 25, 2009 at 8:33 am
You're welcome Regina. To understand what is happening, I suggest you walk through it one step at a time to see the outputs from the CTEs, what is getting stored in the #temp table, etc. Once you understand the various components/concepts, it really isn't that complicated. Please post back here if you have any questions at any point in the process.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 8:57 am
One thing I did not is the hardcoded names and of course I will not be able to do that in the procedure. How would I replace that.
August 25, 2009 at 9:15 am
I think I must be misunderstanding your last post. The "hardcoded" names were just used to create some sample data. Any query that results in three columns similar to those in the @sample table I created should work the same way, you can just add the ROW_NUMBER() function and have it reference those columns instead.
Or are you talking about the names for the column headings? They are not hardcoded. They are being created dynamically from the values in the table by appending sequence numbers to the contact type.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 9:27 am
My mistake, yes I'm sorry I was referring to the column headings. I am having a crazy day at work and read your posting quickly. I think I will be all set with this and I thank you so much for helping me.
August 25, 2009 at 9:30 am
No problem. Good luck to you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 9:38 am
Thanks again.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply