Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to query by grouping one column

    Hello, everyone:

    I have a table like:

    Col1 Col2
    1 A
    2 B
    1 D
    1 P
    2 F
    2 W

    How to query this table to return by Col1 like

    Col1 Col2
    1 A,D,P
    2 B,F,W

    Thanks a lot
    ZYT

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1, 'A' UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT 1, 'D' UNION ALL
    SELECT 1, 'P' UNION ALL
    SELECT 2, 'F' UNION ALL
    SELECT 2, 'W' UNION ALL
    SELECT 3, 'X' UNION ALL
    SELECT 3, 'Y' UNION ALL
    SELECT 4, 'Z' UNION ALL
    SELECT 5, 'O'
    GO
    
    DECLARE @MAX_Col1 int, @Col1 int, @strCol2 varchar(8000)
    DECLARE @rs table(Col1 int, strCol2 varchar(8000))
    
    SELECT @MAX_Col1 = MAX(Col1), @Col1 = MIN(Col1) FROM myTable99
    
    WHILE @MAX_Col1 > = @Col1
      BEGIN
    	SELECT @strCol2 = COALESCE(@strCol2 + ', ','') + Col2 FROM myTable99 WHERE Col1 = @Col1
    	INSERT INTO @rs(Col1, strCol2) SELECT @Col1, @strCol2
    	SELECT @Col1 = MIN(Col1), @strCol2 = null FROM myTable99 WHERE Col1 > @Col1
      END
    
    SELECT * FROM @rs
    GO
    	
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your code is always so thorough. I bet your desk is spotless.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Naw, not spotless, nut definetly not a pig sty

    I need to stay organized though...I hate to have to remember things...

    Except where the tequila is....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you forget that, you probably shouldn't be having any more anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See!

    No Need to remember
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    To Brett Kaiser
    Why so difficult?
    You can create function:
    CREATE FUNCTION GetAllOnLine(@id int)
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    DECLARE @Result VARCHAR(8000)
    SET @Result = ''

    SELECT @Result = @Result + col2 + ' ,'
    FROM YourTable
    WHERE col1=@id

    RETURN @Result
    END

    And use it:

    SELECT Col1, GetAllOnLine(Col1)
    FROM YourTable

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...needs a little cleaning up....

    Code:
    CREATE FUNCTION GetAllOnLine(@id int)
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    DECLARE @Result VARCHAR(8000)
    
    SELECT @Result = COALESCE(@Result + ', ','') + col2
    FROM myTable99
    WHERE col1=@id
    
    RETURN @Result
    END
    GO
    
    SELECT Col1, dbo.GetAllOnLine(Col1) FROM MyTable99
    But don't you double the access paths to the table? One for the UDF and another for the SELECT? If it's a lot of rows I think mine would be more effecient...(though I still would like to know why anyone would like to do this)

    Also, I don't usually like to make my UDF's some very specific, I prefer them to be as generic as possible. You could only use this for one very specific case.

    Do you mind if I blog your solution as another example?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    This is execution plan of my result query:
    |--Compute Scalar(DEFINE[Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
    |--Hash Match(Aggregate, HASH[Mail].[CC]), RESIDUAL[Mail].[CC]=[Mail].[CC]))
    |--Clustered Index Scan(OBJECT[MWolf_Mail].[dbo].[Mail].[PK_Mail]))
    It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.

  10. #10
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    Oh. Forum engine replace my plan on smile!!!
    :-()

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    With code tags perhaps...very nice plan..do you mind if I blog this with the appropriate credit?

    Code:
    This is execution plan of my result query:
      |--Compute Scalar(DEFINE:([Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
           |--Hash Match(Aggregate, HASH:([Mail].[CC]), RESIDUAL:([Mail].[CC]=[Mail].[CC]))
                |--Clustered Index Scan(OBJECT:([MWolf_Mail].[dbo].[Mail].[PK_Mail]))
    It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    If i understood you correctly, you want to post my example somewhere for others to see it and learn (please apologize my bad English)
    If i'm right i have no objections else please correct me

Posting Permissions

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