Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unanswered: HELP - Combining Rows in a View

    Hi All,

    I can do this in Access, with VB, but I'm pretty new to SQL Server.

    Say you have the following table, call it TblStudents:

    Grade Name
    8 John
    8 Mike
    8 Ed
    9 Tom
    9 Greg
    10 Jack
    10 Tony

    And you wanted a view that would give you:

    Grade Name
    8 John, Mike, Ed
    9 Tom, Greg
    10 Jack, Tony

    How would you do this in SQL Server?

    Thanks.

    Henry

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Remember, you asked! I'd use:
    Code:
    CREATE TABLE tHenry (
       grade	INT
    ,  name		VARCHAR(20)
       )
    
    INSERT INTO tHenry (grade, name)
       SELECT     8, 'John'
       UNION ALL SELECT  8, 'Mike'
       UNION ALL SELECT  8, 'Ed'
       UNION ALL SELECT  9, 'Tom'
       UNION ALL SELECT  9, 'Greg'
       UNION ALL SELECT 10, 'Jack'
       UNION ALL SELECT 10, 'Tony'
    GO
    
    CREATE FUNCTION dbo.fHenry(@piGrade INT) RETURNS VARCHAR(200) AS
    BEGIN
    DECLARE @cList	VARCHAR(8000)
    
    SELECT @cList = Coalesce(@cList + ', ' + name, name)
       FROM tHenry
       WHERE  grade = @piGrade
    
    RETURN @cList
    END
    GO
    
    CREATE VIEW vHenry AS SELECT DISTINCT TOP 100 PERCENT
       grade, dbo.fHenry(grade) AS students
       FROM tHenry
       ORDER BY grade
    GO
    
    SELECT * FROM vHenry
    GO
    
    DROP VIEW vHenry
    DROP FUNCTION dbo.fHenry
    DROP TABLE tHenry
    -PatP

Posting Permissions

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