none
Tsql question rows to columns

    Question

  • Lets say for an example I have  a parent table with the ParentID(PK), ParentName etc..

    I then have a child table with ChildID(PK), ParentID(FK), ChildName.

    These tables are part of an existing database I inherited so I cannot change the design.

    I need to create a query which will select each parent and their corresponding children all in one row.

    So the columns heading will be

    ParentID, ParentName, Child1Name, Child2Name, Child3Name etc.....................

    How can I accomplish this? Is there also a way to combine the Child1Name, Child2Name etc into one column comma delimited?


    • Edited by masAndrew Wednesday, June 18, 2014 11:27 PM
    Wednesday, June 18, 2014 11:25 PM

Answers

  • Is there also a way to combine the Child1Name, Child2Name etc into one column comma delimited?

    Try

    -- code #1
    CREATE FUNCTION CatChildName (@ParentID int) 
      returns varchar(2000) as 
    begin
    declare @ChildName varchar(2000);
    set @ChildName= space(0);
    SELECT @ChildName+= ChildName +', '
      from Child as C
      where C.ParentID = @ParentID
    --order by ChildName; IF (Datalength(@ChildName) > 0) set @ChildName= Left(@ChildName, Datalength(@ChildName) -2); return @ChildName; end;
    go SELECT ParentID, ParentName, dbo.CatChildName(ParentID) as ChildNames from Parent;
     

    or

    -- code #2 v3
    ;with
    Tab3 as (
    SELECT ParentID, ParentName,
           (SELECT T2.ChildName + ', ' AS [text()]
              from Child as T2
              where T2.ParentID = T1.ParentID
              --order by T2.ChildName
              for XML PATH ('')
            ) as ChildNames
      from Parent as T1
    )  
    SELECT ParentID, ParentName, Coalesce(Left(T3.ChildNames, Len(T3.ChildNames)-1), '') as ChildNames
      from Tab3 as T3;
     

     



        José Diz     Belo Horizonte, MG - Brasil


    Thursday, June 19, 2014 12:15 AM

All replies