Dynamically convert rows to cloumns

• Saran

  Right there with Babe

  Points: 760

  More actions

  December 27, 2017 at 4:30 am

  #341417

  Hi All,
  Need a help on the following.
  I have a the following code it returning the result : db1,db2    2017-12-27 14:09:15.000    \\share1\test1_F.bak,\\share1\test1_A.bak.bak

  But i want to insert each parameters to a table given by the user.
  Ex: 
  insert into tbl @manual_dbname paramter1,getdate(), @manual_path1
   insert into tbl @manual_dbname paramter2,getdate(), @manual_path2
  like:
  db1 2017-12-27 14:09:15.000  \\share1\test1_F.bak
  db2 2017-12-27 14:09:15.000 \\share1\test1_A.bak.bak

  code:
  declare @manual_dbname varchar (1000)
  declare @manual_backupdate varchar (1000)
  declare @manual_path varchar (1000)

  set @manual_dbname ='db1,db2'
  set @manual_backupdate ='2017-12-12 14:09:15.000'
  set @manual_path ='\\share1\test1_F.bak,\\share1\test1_A.bak.bak'

  select @manual_dbname,@manual_backupdate,@manual_path

  SELECT SUBSTRING(@manual_dbname,0,CHARINDEX (',',@manual_dbname,0))

  Thanks for the help.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 27, 2017 at 5:44 am

  #1973560

  You'll need to use a splitter function to break your strings down into their individual elements, something like this (not tested because I don't actually have the splitter function on my server):

  WITH DBs AS (
    SELECT Item AS DBName, ItemNumber
    FROM dbo.DelimitedSplit8k(@manual_dbname,',')
    )
,   Paths AS (
    SELECT Item AS BackupPath, ItemNumber
    FROM dbo.DelimitedSplit8k(@manual_path,',')
    )
SELECT
     d.DBName
,    @manual_backupdate AS BackupDate
,    p.BackupPath
FROM DBs d
JOIN Paths p ON d.ItemNumber = p.ItemNumber

  John

• Saran

  Right there with Babe

  Points: 760

  More actions

  December 27, 2017 at 6:19 am

  #1973567

  Thanks a lot John. It's perfectly working.