Asked By
mike.jarlod
200 points
N/A
Posted on - 06/25/2011
Hi all,
I have a SQL table named “Employee”. The table consists of the fields: Emp_Id, Emp_date, Emp_msg. The Emp_Id column can have more than one same entry in the table. The fields are separated by ‘|’.
Example: data in the SQL table
231| 10102009| T1
231| 11102009| T2
231| 11102009| T3
I have tried to write a select statement that will put these each Emp_Id in one line.
Example: data of the resultant output.
e.g.
231| 10102009| T1| 11102009| T2| 11102009| T3
It was difficult to write a query for this. Could anyone help me write a query for this?
Thanks.
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.
