SQL Server 2005: How to create a new column from comma-delim data across 2 columns

Question

I need to display this data next to each other.

In other words something like this ...

NewColumn
TECNQ221 3/03/2017 11:10am, TECNQ174 3/03/2017 12:59pm, TECNQ174 3/03/2017 2:04PM, etc

So basically putting the first element from ModificationStaff next to ModificationDates and separated by a comma, all inside a VARCHAR(MAX) inside a single column.

Thanks

Answer 1

Check This.

        SELECT  staffID, 
        substring(( select ','+ concat(s.Data,a.Data)  AS 'data()' 
         from
        (
            SELECT A.staffID,  
            Split.a.value('.', 'VARCHAR(100)') AS Data  ,
            row_number() over (order by (select 1) ) as rank
         FROM  
         (
             SELECT staffID,  
                 CAST ('<M>' + REPLACE(modificationstaff, ',', '</M><M>') + '</M>' AS XML) AS Data  

             FROM  #table
         ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
         )s  inner join

          (SELECT A.staffID,  
             Split.a.value('.', 'VARCHAR(100)') AS Data  ,
             row_number() over (order by (select 1) ) as rank

         FROM  
         (
             SELECT staffID,  
            CAST ('<M>' + REPLACE(modificationdate, ',', '</M><M>') + '</M>' AS XML) AS Data  
             FROM  #table
         ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
         )a   on s.rank=a.rank                       
        FOR XML PATH('') 
        ), 2, 1000)  as NewColumn
        FROM  #table

OutPut :

If You dont have function Split() then first create it.

create FUNCTION [dbo].[Split]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END