Complicated select and string together

I have 2 tables "Lead" and "Deal"
the join on them is leadid

lead has fewer than deal

So what I want is an output of id from lead, and multiple rows, multiple columns strung togther from deal

Example:
lead table
id
1234

deal table

id       dealid    status     rcvd
1234  2310      Dropped  2011-01-01
1234  3254      InProc     2011-12-01

Desired output

id          information
1234     (2310/Dropped/2011-01-01), (3254/InProc/2011-12-01)
Larry Bristersr. DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
try
select distinct 
	a.id
	stuff((select ', (' + cast(dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), rcvd, 120) + ')' from Deal where leadid = a.leadid order by 1 for xml path('')), 1, 2, '')
from lead a

Open in new window

0
ralmadaCommented:
of course name the column
select distinct 
	a.id
	stuff((select ', (' + cast(dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), rcvd, 120) + ')' from Deal where leadid = a.leadid order by 1 for xml path('')), 1, 2, '') as information
from lead a

Open in new window

0
Larry Bristersr. DeveloperAuthor Commented:
ralmada
I've got something screwed up here.

The output puts everything on one line
I can't figure out how to break out by @leadID.leadid
Declare @leadID Table 
(
	leadID varchar(36) null
)

Insert into @leadID (leadID)
Select d.LeadID from proc_ss.dbo.P_Deals d
where len(d.leadID) = 36
group by d.LeadID

select distinct 

	stuff((select ', (' + cast(d.dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), e.AppReceived, 120) + ')' 
	from proc_ss.dbo.p_deals d
	left join proc_ss.dbo.v_Events e on d.DealID = e.DealID
	left join @leadID a on d.LeadID = a.leadid
	order by 1 for xml path('')), 1, 2, '')

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ralmadaCommented:
and by the way wrong column names there in line 3 sorry it should be id and not leadid.
select distinct 
	a.id
	stuff((select ', (' + cast(dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), rcvd, 120) + ')' from Deal where id = a.id order by 1 for xml path('')), 1, 2, '') as information
from lead a

Open in new window

0
Larry Bristersr. DeveloperAuthor Commented:
If i add it as a column I get the message

Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "a.leadid" could not be bound.
0
ralmadaCommented:
I'm not sure why you have the table variable there. but please give this a try


Declare @leadID Table 
(
	leadID varchar(36) null
)

Insert into @leadID (leadID)
Select d.LeadID from proc_ss.dbo.P_Deals d
where len(d.leadID) = 36
group by d.LeadID

select distinct
	a.leadid,
	stuff((select ', (' + cast(d.dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), e.AppReceived, 120) + ')' 
	from proc_ss.dbo.p_deals d
	left join proc_ss.dbo.v_Events e on d.DealID = e.DealID
	where d.LeadID = a.leadID
	order by 1 for xml path('')), 1, 2, '')
from @leadID a

Open in new window

0
ralmadaCommented:
oops, sorry, like this
Declare @leadID Table 
(
	leadID varchar(36) null
)

Insert into @leadID (leadID)
Select d.LeadID from proc_ss.dbo.P_Deals d
where len(d.leadID) = 36
group by d.LeadID

select distinct
	d.leadID,
	stuff((select ', (' + cast(d.dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), e.AppReceived, 120) + ')' 
	from proc_ss.dbo.p_deals d
	left join proc_ss.dbo.v_Events e on d.DealID = e.DealID
	where d.LeadID = a.leadID
	order by 1 for xml path('')), 1, 2, '')
from @leadID a

Open in new window

0
ralmadaCommented:
oh well,
Declare @leadID Table 
(
	leadID varchar(36) null
)

Insert into @leadID (leadID)
Select d.LeadID from proc_ss.dbo.P_Deals d
where len(d.leadID) = 36
group by d.LeadID

select distinct
	a.leadID,
	stuff((select ', (' + cast(d.dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), e.AppReceived, 120) + ')' 
	from proc_ss.dbo.p_deals d
	left join proc_ss.dbo.v_Events e on d.DealID = e.DealID
	where d.LeadID = a.leadID
	order by 1 for xml path('')), 1, 2, '')
from @leadID a

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
Took forever to run (50 seconds for 1800 rows) but correct.
I can run and place in a table for report use first thing each morning

Thanks
0
ralmadaCommented:
yeah, it's quite a dog because you have the table variable there. but you might consider getting rid of it and do something like this in just one query
select distinct
	a.leadID,
	stuff((select ', (' + cast(d.dealid as varchar(max)) + '/' + status + '/' + convert(varchar(10), e.AppReceived, 120) + ')' 
	from proc_ss.dbo.p_deals d
	left join proc_ss.dbo.v_Events e on d.DealID = e.DealID
	where d.LeadID = a.leadID
	order by 1 for xml path('')), 1, 2, '')
from proc_ss.dbo.P_Deals a
where len(a.leadID) = 36

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.