how to get three rows and display in one rows !?

• bernardchristophe

  Old Hand

  Points: 366

  More actions

  November 12, 2009 at 6:13 am

  #142988

  Hi,

  i work with sql server 2005

  i have two table as this

  table tpositions

  (idPosition primary key, positionId int)

  table tpersonnes

  (idPersonne primarey key, nom varchar(20), prenom varchar(50), position int)

  i have some data in these two tables

  ok i do a select on a position exemple 2886

  select * from tpersonnes where position = 2886

  i have three records for this query

  and i 'd like to have just one row !!!!!!!

  all name and surname must be in one row

  example with my query i have this result

  (select * from tpersonnes where position = 2886)

  2886, nom 1 , prenom 1

  2886, nom 2, prenom 2

  2886, nom 3, prenom 3

  ok and i want this

  2886, nom1 preom 1 / nom 2 prenom 2 / nom 3 prenom 3

  thanks for your sample and your time for the beginner thant i 'm 😉

  Christ

• Mike01

  SSChampion

  Points: 11395

  More actions

  November 12, 2009 at 6:33 am

  #1078307

  if you set the results to a variable, then you can try this

  declare @tper varchar(max), @pos int

  set @tper = ''

  select @pos = position, @tper = @tper + nom + ' ' + prenom + '/' from tpersonnes

  where position = 2886

  select @pos, @tper

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  November 12, 2009 at 6:37 am

  #1078311

  Are you looking for something like this?

  CREATE table #t1(ID int, FName varchar(10), Sname varchar(10))

  INSERT INTO #t1 VALUES (2886, 'nom 1' , 'prenom 1')

  INSERT INTO #t1 VALUES (2886, 'nom 2' , 'prenom 2')

  INSERT INTO #t1 VALUES (2886, 'nom 3' , 'prenom 3')

  INSERT INTO #t1 VALUES (2887, 'nom 1' , 'prenom 1')

  Select ID,

  STUFF((SELECT '/'+ FNAME + ' ' + SNAME FROM #t1 t2

  WHERE t2.id = t1.id for xml path('')), 1, 1, '')

  from

  #t1 t1

  Group by id

  ---------------------------------------------------------------------------------

• bernardchristophe

  Old Hand

  Points: 366

  More actions

  November 12, 2009 at 6:51 am

  #1078321

  Hi both,

  thanks for your quick reply !

  your are too strong..

  thanks for all

  Christ