Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    50

    Unanswered: SQL Server query, are you able to bunch together child table fields.

    Hi all,

    I'm using SQL Server 2005 along side Visual Studio 2005, using VB to create a web application at work.
    Now I'd like to bunch together some child fields in a gridview when displayed. Have a look at the attached pic to show what I would like.

    Is this possible via a query or would I need to set something up on the gridview to do this?

    Cheers,

    Paul.
    Attached Thumbnails Attached Thumbnails sql table.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yes it is possible.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2007
    Posts
    50
    Excellent, any chance of a little example?

    Cheers,

    Paul.

  4. #4
    Join Date
    Jul 2007
    Posts
    50

    Wink

    Any one gonna give me a clue?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want the query results to have blanks for second and subsequent occurrences of the parent, the answer is no, you can't do it

    otherwise, it's just a simple join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2008
    Posts
    5

    Below is one of many solutions (usually stupid & unefficient)

    DECLARE @pn varchar(50), @cn VARCHAR(50), @cc varchar(5), @last varchar(50), @pID int, @cID int
    DECLARE @t table ([Parent Name] varchar(50), [Child Name] varchar(50), [Child Completed] varchar(5))
    DECLARE cu CURSOR FOR SELECT p.NAME AS [Parent Name],c.Name AS [Child Name], c.Completed AS [Child Completed]
    FROM [Parent Table] AS p LEFT JOIN [Child Table] AS c ON p.ID = c.[Parent ID]
    ORDER BY p.ID, c.ID
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    OPEN cu
    FETCH NEXT FROM cu INTO @pn, @cn, @cc
    IF @@FETCH_STATUS <> 0 PRINT 'No records';
    SELECT @last = ''

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @last = @pn SELECT @pn = ''
    ELSE SELECT @last = @pn
    INSERT INTO @t VALUES (@pn, @cn, @cc)
    FETCH NEXT FROM cu INTO @pn, @cn, @cc
    END

    CLOSE cu
    DEALLOCATE cu

    SELECT [Parent Name], [Child Name], [Child Completed] FROM @t
    GO
    -- -----------------------------------------------------------------
    Regards,

    Z. Ch.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are really two questions here... For the question: "Can you do this in SQL?" the answer is yes if the child ID values are unique. For the question "Should you do this in SQL?" the answer is always no.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, creative application of SQL allows you to do many stupid things. As an exercise in pure MSQLbation, it may be entertaining, but I wouldn't put such code out in production. Certain activities must remain private according to all standards of taste and good conduct.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jul 2007
    Posts
    50
    Cheers all...

    I'll look at the gridview then...

  10. #10
    Join Date
    May 2008
    Posts
    5

    ROW_NUMBER() OVER (PARTITION BY... solves the problem

    -- ================================================== ======
    -- Author: Z. Ch.
    -- Create date: 2008-05-29
    -- Description: MrRalphMan's Problem rational solution
    -- ================================================== ======
    --
    SELECT CASE WHEN ROW_NUMBER() OVER (PARTITION BY [Parent ID] ORDER BY [Parent ID]) = 1 THEN p.NAME ELSE '' END AS [Parent Name],
    c.Name AS[Child Name], c.Completed AS [Child Completed]
    FROM [Parent Table] AS p LEFT JOIN [Child Table] AS c ON p.ID = c.[Parent ID]
    ORDER BY p.ID, c.ID

Posting Permissions

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