Query Help

• ramesh_kondaparthy

  Ten Centuries

  Points: 1069

  More actions

  July 15, 2004 at 6:05 pm

  #108790

  Hi All,

  I have a table T1 with 2 columns.

  T1

  EmpId          PhNo

  1                 233

  1                 444

  2                 455

  2                 667

  5                 3333 

   

  Now i need the o/p of the query as

  EmpId   Phno

  1         233,444

  2         455,667

  5          3333

   

  Please help me in getting this output in a single query. Is it possible to get the output without using cursor and Stored procedure.

   

  Thanks,

  Ramesh K

   

   

   

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 15, 2004 at 6:42 pm

  #514786

  hey,

  i think this is what you are looking for:

  SELECT

    e.EmpId,

    PhNo = '' + CASE WHEN e.PhNo IS NOT NULL THEN e.PhNo + ',' ELSE '' END

  FROM

    T1 e

  GROUP BY

    e.EmpId

  hope this helps ...

• Bavineni

  SSC Enthusiast

  Points: 147

  More actions

  July 16, 2004 at 10:00 am

  #514899

  If you can use the user defined function, you can try this solution....

  1. Define a function

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

  CREATE FUNCTION dbo.spGetList

  (

  @Empid varchar(10)

  ) 

  RETURNS varchar(100) AS 

  BEGIN

  declare @list varchar(100)

  set @list=''

  select @list=@list+ ','+ Phno from t1 where empid = @Empid

  --select  @list

  set @list=right(@list,len(@list)-1)

  return @list

  END

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

  2. use the function in query:

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

  select distinct Empid,dbo.spgetlist(Empid) from T1

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

   

   

   

• rockmoose

  SSCarpal Tunnel

  Points: 4601

  More actions

  July 16, 2004 at 12:09 pm

  #514925

  Hey JP,

  I really would have loved it if your solution worked !?

  Wouldn't that have been nice 🙂

  /rockmoose

  
  You must unlearn what You have learnt