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?
 
ralmadaConnect With a Mentor Commented:
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.