multiple column in one column with same id

• freecoder

  SSC Eights!

  Points: 951

  More actions

  January 17, 2012 at 1:26 am

  #1434450

  create table mytable (id int identity(1,1), PersonID int, Unit varchar(10))

  insert into mytable values (1,'Che')

  insert into mytable values (1,'Mat')

  insert into mytable values (1,'Phy')

  insert into mytable values (2,'Che2')

  insert into mytable values (2,'Mat2')

  insert into mytable values (2,'Phy2')

  insert into mytable values (3,'Phy3')

  SELECT t1.PersonID,

  Units =REPLACE( (SELECT Unit AS [data()]

  FROM mytable t2

  WHERE t2.PersonID = t1.PersonID

  ORDER BY Unit

  FOR XML PATH('')

  ), ' ', ',')

  FROM mytable t1

  GROUP BY PersonID ;

  <asp:TemplateField HeaderText="Units" SortExpression="Units">

  <ItemTemplate>

  <%# DataBinder.Eval(Container.DataItem, "units").ToString().Replace(",", "< b r>")%>

  </ItemTemplate>

  </asp:TemplateField>