CODEWITHSUNDEEP
sqlsql-serverpivotunpivot
Fandango68
Fandango68

Reputation: 4848

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

I need to display this data next to each other.

enter image description here

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

Upvotes: 0

Views: 85

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

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 :

enter image description here

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

Upvotes: 1

Murad
Murad

Reputation: 543

Create a function that accepts two parameter ModificationStaff and ModificationDates. Inside this function split modificationstaff and modificationdate. Then combine same positions of splitted object. (For splitting text use this method : T-SQL split string) .Returning value of that function will be

TECNQ221 3/03/2017 11:10am, TECNQ174 3/03/2017 12:59pm, TECNQ174 3/03/2017 2:04PM etc.
Call this function during the selection (select datasaved, staffid, testfunction(ModificationStaff,ModificationDates) from db)

Upvotes: 2

Related Questions