Results 1 to 10 of 10

Thread: distict

  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: distict

    This works

    DECLARE @list12 varchar(100)
    BEGIN
    SELECT @list12= PlaceName + COALESCE (@List12 + ',','')
    FROM Names

    END;



    but this doesn't works


    DECLARE @list12 varchar(100)
    BEGIN
    SELECT @list12=DISTINCT PlaceName + COALESCE (@List12 + ',','')
    FROM Names

    END;


    I want a distinct concatenated comma separated list, WITHOUT using a subquery , i.e first extracting distinct and than concatenating, no I dont want that.


    Is there any way out, because something like this has to be done with a biiig query that itself uses a subquery

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --

    DECLARE @list12 varchar(100)
    BEGIN
    SELECT DISTINCT @list12 = PlaceName + COALESCE(@List12 + ',','')
    FROM Names
    END;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    Thank you but

    No this didn;t worked.....it gives me some strange output..not strange but I just returns 1 result..one of the column values...nothing concatenated ....when the table has more than 300.

    Please some other suggestions..
    I want concatenated list ( comma separated) of one of the columns....but duplicates removed and just their one copy should exist.
    i do not want to use subquery that is retrieve all than remove duplicate.

    The above one...strangely does not give error thought but also does not give desired result. so may be some twist should work..any idea?

    thank you
    Last edited by cimon; 04-15-09 at 10:52.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You may not want to use a subquery, but it is the answer you're looking for...
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @c varchar(max)
    
    DECLARE @table table (
       some_field char(1)
    )
    INSERT INTO @table (some_field)
              SELECT 'A'
    UNION ALL SELECT 'B'
    UNION ALL SELECT 'B'
    UNION ALL SELECT 'C'
    UNION ALL SELECT 'D'
    UNION ALL SELECT 'D'
    
    --Reg'lah
    SELECT @c = Coalesce(@c + ', ', '') + some_field
    FROM   @table
    
    PRINT @c
      SET @c = NULL
    
    --Distinct
    SELECT DISTINCT @c = Coalesce(@c + ', ', '') + some_field
    FROM   @table
    
    PRINT @c
      SET @c = NULL
    
    --Subquery
    SELECT @c = Coalesce(@c + ', ', '') + some_field
    FROM   (
            SELECT DISTINCT
                   some_field
            FROM   @table
           ) As a_subquery
    
    PRINT @c
      SET @c = NULL
    P.S. What version of SQL Server are you using?
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    2005 onwards only:
    Code:
    SELECT Stuff(
                 (SELECT DISTINCT ', ' + some_field
                  FROM   @table
                  FOR XML PATH (''))
           , 1, 2, '')
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee
    2005 onwards only
    george, thanks

    you young guns are fabulous, and my best wishes go with you (i learned on 4.2, and sort of caught up at 2000, but fell to the wayside after that)

    still, that's a hack, don't you think? i mean, what's XML got to do with this?

    are you familiar with mysql's GROUP_CONCAT?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As a reader of the daily WTF, you should know that everything is better with XML. It is kind of like ketchup for the programming world ;-).

    Now, if he can just work in some JavaScript....

    On a slightly more serious note, GROUP_CONCAT is possible in SQL 2005, and may even be available somewhere. SQL 2005 introduces CLR functions (functions written in .NET languages), including aggregate functions. You may have to write it yourself, but it is possible.


    EDIT: OK, it is the example on the help page. Not a lot of writing left to do: http://technet.microsoft.com/en-us/l.../ms131056.aspx

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Absolutely! A complete and utter hack!

    I don't like that method particularly, but it does outperform the recursive variable assignment and can be used inline (e.g. comma delimited exams marks per student) without the need of being tuurned in to a UDF.

    And of course I've heard of group_concat, I think you'll find I praised MySQL extrememly highly for this function a mere month or two ago
    George
    Home | Blog

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by MCrowley
    EDIT: OK, it is the example on the help page. Not a lot of writing left to do: Invoking CLR User-Defined Aggregate Functions
    As yet there is no such thing as an ordered aggregate function in SQL Server. So with a concatenation aggregate you can't control the order in which the data gets strung together. That's the big advantage of GROUP_CONCAT in MySQL and the FOR XML solution in SQL Server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •