May 9, 2011 at 11:28 am
Ok...
So now... Imagine we pull the recordset above... Then in a loop a have another select (the clientid of 11145 would normally equal the clientid from last select)
SELECT Deductions.PolicyNumber1,Deductions.Amount,Deductions.ProviderID,
Providers.Name,Deductions.GroupID,Providers.ProviderID AS ProvID,
Providers.Type AS ProvType, ProviderGroups.GroupNumber
FROM Deductions,Providers,ProviderGroups WHERE Deductions.ProviderID=Providers.ProviderID
AND Deductions.ClientID=11145 And RevisedRate=0 AND ProviderGroups.GroupID=Deductions.GroupID
ORDER BY Providers.Type
Ok this returns multiple rows... Which need to be assembled into one string. (hope this isnt too complicated)
Here is a classic ASP piece of code showing what line is assembled for each record from the first select we were working on
BFour=""
Do while not ClientRec.Eof
BFour=BFour & ClientRec("Name") & ","
ClientRec.MoveNext
Loop
That would give me a list of providers for those clients like "Provider A, Provider B"
Can we get this combined into one sql statement with what we were doing before, or am I asking too much?
I am trying to figure out the correct way to go about this... So I can learn from this and know going forward...
Problem I am having is this is a very large report, and thats a lot of records to loop through and assemble each time. It worked in SQL 2000 to use brute force, but sql 2005 seems to start refusing connections if you use too many queries in a loop.
May 9, 2011 at 11:30 am
I'll let you figure this on out... or correct lowell's typos :w00t:. Yes uncalled-ish for ;-).
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
May 9, 2011 at 11:32 am
I've seen context like this, and was reading up on it... But can I join something like that into what I have above, or will I need to pull that query each loop?
May 9, 2011 at 11:35 am
Bear with me... I know I'm a newbie... But would I just assemble the needed sql statement to assemble my string then join it in on the bottom with a LEFT OUTER JOIN ?
May 9, 2011 at 11:40 am
mikeincape (5/9/2011)
Bear with me... I know I'm a newbie... But would I just assemble the needed sql statement to assemble my string then join it in on the bottom with a LEFT OUTER JOIN ?
No need to loop. Look at my sample again.
There's a base table (query), then there's the concatenated column that runs another query.
I know it's not too obvious, but you need to understand that code or you won't be able to support it @ 3am when something breaks.
May 9, 2011 at 11:44 am
No need to loop. Look at my sample again.
I know there is no need to loop.... Please look at my question again... It is about adding it to the existing SQL statement selecting the clients we worked on above.
Bear with me... I know I'm a newbie... But would I just assemble the needed sql statement to assemble my string then join it in on the bottom with a LEFT OUTER JOIN ?
Meaning, using your example, make my own SQL statement to assemble my string... I understand that...
What I am wondering is will i then join it with a left outer join on the bottom of the SQL statement we've been working on the whole time?
May 9, 2011 at 11:47 am
Ninja's_RGR'us (5/9/2011)
I'll let you figure this on out... or correct lowell's typos :w00t:. Yes uncalled-ish for ;-).
😀 only uncalled for when it's not true!
anyway, fix the syntax issues in this mess and it might be what yuo are looking for:
SELECT
Deductions.PolicyNumber1,
Deductions.Amount,
Deductions.ProviderID,
Deductions.GroupID,
OuterProviders.ProviderID AS ProvID,
OuterProviders.Type AS ProvType,
ProviderGroups.GroupNumber,
STUFF((
SELECT
',' + Providers.Name
FROM Providers
WHERE Providers.ProviderID = Deductions.ProviderID
ORDER BY Providers.Name
FOR XML PATH('')
) , 1 , 1 , '') As [Columns_]
FROM Deductions
INNER JOIN Providers OuterProviders
ON Deductions.ProviderID = OuterProviders.ProviderID
INNER JOIN ProviderGroups
ON ProviderGroups.GroupID = Deductions.GroupID
WHERE Deductions.ClientID = 11145
AND RevisedRate = 0
GROUP BY
Deductions.PolicyNumber1,
Deductions.Amount,
Deductions.ProviderID,
Deductions.GroupID,
OuterProviders.ProviderID,
OuterProviders.Type,
ProviderGroups.GroupNumber
ORDER BY OuterProviders.[Type]
Lowell
May 9, 2011 at 11:48 am
No, your whole left join goes into a subquery for the column only.
You need to correlate the base query with the column's subquery so that you only get 1 client, or whatever per select, but that's it.
I'll let you hack at it... I'm not serving you by doing the work for you.
May 9, 2011 at 11:49 am
I just want to thank you guys... You have been a huge help.... I have been reading up a lot on this stuff, but to see my data and the sql queries together, really helps me understand how and what is going on.
Thanks again. 😀
May 9, 2011 at 11:50 am
Ninja's_RGR'us (5/9/2011)
No, your whole left join goes into a subquery for the column only.You need to correlate the base query with the column's subquery so that you only get 1 client, or whatever per select, but that's it.
I'll let you hack at it... I'm not serving you by doing the work for you.
Not looking for you to do my work for me...
Thanks for the help you provided.
May 9, 2011 at 11:50 am
Lowell (5/9/2011)
Ninja's_RGR'us (5/9/2011)
I'll let you figure this on out... or correct lowell's typos :w00t:. Yes uncalled-ish for ;-).😀 only uncalled for when it's not true!
anyway, fix the syntax issues in this mess and it might be what yuo are looking for:
SELECT
Deductions.PolicyNumber1,
Deductions.Amount,
Deductions.ProviderID,
Deductions.GroupID,
OuterProviders.ProviderID AS ProvID,
OuterProviders.Type AS ProvType,
ProviderGroups.GroupNumber,
STUFF((
SELECT
',' + Providers.Name
FROM Providers
WHERE Providers.ProviderID = Deductions.ProviderID
ORDER BY Providers.Name
FOR XML PATH('')
) , 1 , 1 , '') As [Columns_]
FROM Deductions
INNER JOIN Providers OuterProviders
ON Deductions.ProviderID = OuterProviders.ProviderID
INNER JOIN ProviderGroups
ON ProviderGroups.GroupID = Deductions.GroupID
WHERE Deductions.ClientID = 11145
AND RevisedRate = 0
GROUP BY
Deductions.PolicyNumber1,
Deductions.Amount,
Deductions.ProviderID,
Deductions.GroupID,
OuterProviders.ProviderID,
OuterProviders.Type,
ProviderGroups.GroupNumber
ORDER BY OuterProviders.[Type]
Well syntax checks out for me... I'll let you guys finish the finer details... heading out in a couple minutes.
May 9, 2011 at 11:54 am
mikeincape (5/9/2011)
Ninja's_RGR'us (5/9/2011)
No, your whole left join goes into a subquery for the column only.You need to correlate the base query with the column's subquery so that you only get 1 client, or whatever per select, but that's it.
I'll let you hack at it... I'm not serving you by doing the work for you.
Not looking for you to do my work for me...
Thanks for the help you provided.
I know you're doing all you can. That's why this got solved so quickly.
The challenge with the concatenation is that it's really advanced code even if it looks simple. So it's really important that you understand what's going on... hence I preffered to let you sweat at it for a few minutes before spitting out the answer. But lowell already gave you the answer... almost... so enjoy ;-).
May 9, 2011 at 12:07 pm
I am looking into this now to see if I can get it doing what I need... But right now it's pulling 4 rows, each one with a different provider name under columns_ ... End result I need is 1 row with an assembled string...
I am less worried about figuring this query out.... I think I can do it with a little time to play with...
What I am concerned with is combining it with the query we were working with earlier to have one huge query that is pulling me entire recordset along with this assembled string... That is what I am not sure how I am going to do.
Lowell (5/9/2011)
Ninja's_RGR'us (5/9/2011)
I'll let you figure this on out... or correct lowell's typos :w00t:. Yes uncalled-ish for ;-).😀 only uncalled for when it's not true!
anyway, fix the syntax issues in this mess and it might be what yuo are looking for:
SELECT
Deductions.PolicyNumber1,
Deductions.Amount,
Deductions.ProviderID,
Deductions.GroupID,
OuterProviders.ProviderID AS ProvID,
OuterProviders.Type AS ProvType,
ProviderGroups.GroupNumber,
STUFF((
SELECT
',' + Providers.Name
FROM Providers
WHERE Providers.ProviderID = Deductions.ProviderID
ORDER BY Providers.Name
FOR XML PATH('')
) , 1 , 1 , '') As [Columns_]
FROM Deductions
INNER JOIN Providers OuterProviders
ON Deductions.ProviderID = OuterProviders.ProviderID
INNER JOIN ProviderGroups
ON ProviderGroups.GroupID = Deductions.GroupID
WHERE Deductions.ClientID = 11145
AND RevisedRate = 0
GROUP BY
Deductions.PolicyNumber1,
Deductions.Amount,
Deductions.ProviderID,
Deductions.GroupID,
OuterProviders.ProviderID,
OuterProviders.Type,
ProviderGroups.GroupNumber
ORDER BY OuterProviders.[Type]
Viewing 13 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply