merger into one column

• diboy79

  Old Hand

  Points: 308

  More actions

  July 26, 2012 at 9:39 pm

  #96853

  Dear All,

  I have table with structure and sample data as follow:

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#tblloan','U') IS NOT NULL

  DROP TABLE #tblloan

  --===== Create the tblloan table

  CREATE TABLE #tblloan

  (

  accountNumeric (18,0),

  loan_nameVarchar(5),

  loan_numberVarchar(10)

  )

  --===== Insert the test data into the test table

  INSERT INTO #tblloan

  (account, loan_name,loan_number)

  SELECT '1','A','100-ID' UNION ALL

  SELECT '1','B','200-ID' UNION ALL

  SELECT '1','C','300-ID' UNION ALL

  SELECT '2','D','400-ID' UNION ALL

  SELECT '2','E' ,'500-ID'

  I want to merger column loan_name and loan_number with account ref. I expected the result table is:

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#tblloan2','U') IS NOT NULL

  DROP TABLE #tblloan2

  --===== Create the tblloan2 table

  CREATE TABLE #tblloan2

  (

  accountNumeric (18,0),

  loanVarchar(30)

  )

  --===== Insert the test data into the test table

  INSERT INTO #tblloan2

  (account, loan)

  SELECT '1','A 100-ID,B 200-ID,C 300-ID)' UNION ALL

  SELECT '2','D 400-ID,E 500-ID'

  Any ideas would be greatly appreciated.

  thanks for the help:-)

• diboy79

  Old Hand

  Points: 308

  More actions

  July 27, 2012 at 12:20 am

  #1518301

  sorry for early posting:-D,

  this article has helped me..

  http://www.sqlservercentral.com/articles/Test+Data/61572/

  thanks Jeff