STUFF Command Not Working

  • 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
  • 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.

  • I appreciate the help, but there are still two records, instead of PID / PNAME being in the same row in column Profile Details

  • It's fairly hard to tell without tables and data. It doesn't look the query will even run as written.

  • 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.

  • 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:

    • eliminated the distinct in the main query,
    • eliminated the both left joins in the main query,
    • eliminated the group by, as I'm assuming you have a primary key of Period, Sector, Rits_ID on your ReviewStatus table
    • added the [text()] column name to specify that the element is to interpreted as a text() component in the resulting xml,
    • removed the root() clause to further simplify the resulting xml to just a list of text() elemements.
    • improved the xquery search criterium to exactly specify we're looking for a single text() element.
    • replaced the left joins in the sub query by an exists to avoid creating duplicate results from the sub query (This assumes the tables are properly defined with primary keys).
    • added an order by clause in the sub query to force re-producible output
    • To show how to combine the distinct clause with an order by clause in the list of profile_summary rows I added a sub query ( ) t . The distinct on the sub query would however most likely in this example not be needed any more due to replacing the joins in the sub query by an exist clause.
    • had the stuff function not only replace the first result's ';', but also the space you added after it so the profile_details column won't always start with a space.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • Duplicate post due to errors in the forum software

    • This reply was modified 1 week, 3 days ago by  R.P.Rozema.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply