December 7, 2023 at 8:16 pm
I am trying to use the STUFF command, however, the column using the STUFF just seems to just include everything, instead of the ones specific to the user. I am hoping someone can notice what I am doing incorrectly in my SQL Statement. Below is the code I am using.
SELECT
DISTINCT
G.[Period],
G.[Sector],
G.RID,
STUFF
((
SELECT
DISTINCT
'; ' + C.[PID] + ' ' + C.[PName]
FROM
[ReviewStatus] A
LEFT JOIN Profile_Members B ON B.RID = A.RID
LEFT JOIN Profile_Summary C ON C.[PID] = B.MID
FOR XML PATH(''), ROOT('MyString'), TYPE).value('/MyString[1]','varchar(max)'), 1, 1, ''
) AS [Profile Details]
FROM
[ReviewStatus] G
LEFT JOIN Profile_Members M ON M.RID = G.RID
LEFT JOIN Profile_Summary S ON S.[PID] = M.MID
GROUP BY
G.[Period],
G.[Sector],
G.Rits_ID
December 7, 2023 at 8:52 pm
Try this. It may not work because of the GROUPing. You might have to do the GROUPing after concatenating the subquery results.
SELECT
DISTINCT
G.[Period],
G.[Sector],
G.RID,
STUFF
((
SELECT
DISTINCT
'; ' + C.[PID] + ' ' + C.[PName]
FROM Profile_Summary C
WHERE C.[PID] = B.MID
FOR XML PATH(''), ROOT('MyString'), TYPE).value('/MyString[1]','varchar(max)'), 1, 1, ''
) AS [Profile Details]
FROM
[ReviewStatus] G
LEFT JOIN Profile_Members M ON M.RID = G.RID
GROUP BY
G.[Period],
G.[Sector],
G.Rits_ID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 8, 2023 at 1:51 pm
I appreciate the help, but there are still two records, instead of PID / PNAME being in the same row in column Profile Details
December 13, 2023 at 7:06 pm
It's fairly hard to tell without tables and data. It doesn't look the query will even run as written.
December 23, 2023 at 2:02 am
Okay let us examine more closely what you provided cleaning it up just a little as unlike some junk I get it was relatively nicely format:
SELECT DISTINCT
[rs].[Period]
,[rs].[Sector]
,[rs].[RId]
,STUFF( ( SELECT DISTINCT
'; ' + [ps2].[PId] + ' ' + [ps2].[PName]
FROM [ReviewStatus] AS [rs2]
------
LEFT JOIN Profile_Members AS [pm2]
ON [pm2].[RId] = [rs2].[RId]
------
LEFT JOIN Profile_Summary AS [ps2]
ON [ps2].[PId] = [pm2].[MId]
------
FOR XML PATH(''), ROOT('MyString'), TYPE
).VALUE( '/MyString[1]', 'VARCHAR(MAX)' ), 1, 1, ''
) AS [Profile_Details]
------
FROM [ReviewStatus] AS [rs]
------
LEFT JOIN Profile_Members AS [pm]
ON [pm].[RId] = [rs].[RId]
------
LEFT JOIN [Profile_Summary] AS [ps]
ON [ps].[PId] = [pm].[MId]
------
GROUP BY [rs].[Period]
,[rs].[Sector]
,[rs].[Rits_ID];
Next let us look at the definition of the STUFF function
STUFF ( character_expression , start , length , replace_with_expression )
Character_Expression = ( SELECT DISTINCT
'; ' + [ps2].[PId] + ' ' + [ps2].[PName]
FROM [ReviewStatus] AS [rs2]
------
LEFT JOIN Profile_Members AS [pm2]
ON [pm2].[RId] = [rs2].[RId]
------
LEFT JOIN Profile_Summary AS [ps2]
ON [ps2].[PId] = [pm2].[MId]
------
FOR XML PATH(''), ROOT('MyString'), TYPE
).VALUE( '/MyString[1]', 'VARCHAR(MAX)' )
Start = 1
Length = 1
Replace = ''
Okay as it appears you are extracting something and you are removing the first character. Is that correct? Note from your post I got the impression that this is incorrect but did not want to assume.
Next it would be helpful if you supplied an example of a bit of data with a before and after situation of what you expect to achieve with this code. It does not have to be real data just an example of the real data, so that we can determine your starting and ending expected values.
Lastly, and totally unrelated, I have to ask why do you use ID this indicates an anacroym (I.D.) when in fact it is usually an abbreviation for Identity or Identification or the like -- again as an abbreviation it would be rendered Id not ID which again implies I.D. such as Identity Data or the like (aka 2 words not an abbreviation of a single word). Perhaps this sticks out to me because while being a programmer I am also a technical writer, and implications such as this can speak volumes when properly used and can be extremely confusing when improperly used. Thus ID as an abbreviation is improper usage and should thus be Id -- the technically full correct version of both would be I.D. and Id. which makes it obvious that ID <> Identity or Identification while Id = Identity or Identification.
Look forward to hearing from you to potentially help you solve your problem. Remember there is no such thing as a stupid question for someone honestly seeking knowledge -- only stupid answers. Quality answers seek to solve the problem or help to redefine the question to make it solvable. Note this is just me spouting my philosophy and not me pointing fingers at anyone as that is wasted and unnecessary effort. As I am about solutions and help and not criticism.
February 15, 2024 at 2:51 pm
As I see it, this is not a problem related to the stuff() function at all. I think you're missing a condition to limit the rows returned by the sub query to something defined in the main query. In your example, the sub query will return the same set of rows for each row in the main query and thus the stuff function will return the exact same result for each row in the main query.
With no table definitions or further insight in what the intention of the query is I can only guess as to what would be the proper conditions, but here's an attempt. I assumed you intended to show a ;-separated list of profile_summaries C that had a least one Profile_Member B in some ReviewStatus G. If this isn't what you intended I hope you can still use it as an example.
SELECT
G.[Period],
G.[Sector],
G.RID,
STUFF ((
SELECT '; ' + t.[PID] + ' ' + t.[PName] as [text()]
FROM (
SELECT DISTINCT C.[PID], C.[PName]
FROM Profile_Summary C
WHERE EXISTS (
SELECT *
FROM Profile_Members B
WHERE B.MID = C.[PID]
AND B.RID = G.RID
) t
ORDER BY t.PName
FOR XML PATH(''), TYPE
).VALUE('text()[1]', 'varchar(max)'), 1, 2, '') AS [Profile Details]
FROM [ReviewStatus] G
I have also slipped in some improvements/optimizations to aid you in improving your resulting query:
February 15, 2024 at 2:55 pm
Duplicate post due to errors in the forum software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply