Transpose rows to a column

• Vincy-856784

  Ten Centuries

  Points: 1298

  More actions

  June 18, 2010 at 8:18 am

  #222392

  Hello,

  I have a table data as shown below:

  Select * from my table;

  MsgId | ToUserId | FromUserId | Message | ConvId

  1 | touser | fromuser | Hello, how are u | 1

  2 | touser | myuser | I am good thank you | 1

  3 | touser | myuser | How you been, | 1

  4 | touser | myuser | Hello, how are u | 1

  5 | touser | youruser | Hello, how are u | null

  but i want to get the result as shown below:

  ToUserId | FromUserId | Message

  touser | fromuser | Hello, how are u, I am good thank you, How you been, Hello, how are u

  touser | youruser | Hello, how are u

  Any help would be greatly appreciated.

  Thanks,

• Stefan_G

  SSCertifiable

  Points: 6609

  More actions

  June 18, 2010 at 8:41 am

  #1182178

  Try something like this:

  select

  touserid, fromuserid,

  stuff((

  select ' : '+Message

  from MyTable t2

  where t2.touserid=t1.touserid and t2.fromuserid=t1.fromuserid

  order by msgid

  for xml path(''),type

  ).value('.[1]','varchar(max)'), 1, 3, '') as Messages

  from MyTable t1

  group by touserid, fromuserid

  order by touserid, fromuserid

• Vincy-856784

  Ten Centuries

  Points: 1298

  More actions

  June 18, 2010 at 9:36 am

  #1182219

  perfect Stefan_G. It worked just great!

  Thank you very much and have a great day!