Issue in delimiter usage in SQL query

Issue in delimiter usage in SQL query

To implement this concept, you can try this using XML queries in SQL. First create the table as given below.

Create table Emp (Emp_Id int, Emp_date varchar(200), Emp_msg varchar(100))

next insert values using the below query,

Insert into Emp values (231| ‘10102009’| ‘T1’)
Insert into Emp values (231| ‘11102009’| ‘T2’)
Insert into Emp values (231| ‘11102009’| ‘T3’)

Fire the below query to produce the output,

 Select distinct Emp_Id,  concatStr FROM Emp E1 cross apply
   (Select stuff( ( select '|' as text(), cast(Emp_date as varchar(30)) as text(),'|' as text(),
         cast(Emp_msg as varchar(30)) as text() from Emp E2 where
         E2.Emp_Id = E1.Emp_Id for xml path(''),type).value('.','varchar(200)'),1,1,'') ) E2(concatStr)

In this query, sometimes the declared maximum length of the data can create a problem in the execution. If the error occurs, change the maximum length as 255 (which is the max value in the SQL for char data type). If still the error occurs, then try changing the char length manually in SSMS. In the SSMS environment, go to “tools” > “options” > “query results” > “SQL Server” > “results to grid”, and manually increase the option “maximum characters retrieved” to a larger value.