CODEWITHSUNDEEP
sqlsql-server
Ynias Reynders
Ynias Reynders

Reputation: 309

SQL: combine values of columns into one row

Microsoft SQL Server 2014

This is my statement now

SELECT dbo.Request.RequestId, dbo.Employee.Givenname, dbo.Employee.Surname, Employee_1.Surname AS Expr1, Employee_1.Givenname AS Expr2
  FROM dbo.Request 
       INNER JOIN
          dbo.Employee ON dbo.Request.StartedBy_EmployeeId = dbo.Employee.EmployeeId 
       INNER JOIN
           dbo.Employee AS Employee_1 ON dbo.Request.TargetEmployeeId = Employee_1.EmployeeId
  WHERE (dbo.Request.State = '4')

But I don't need the output of the command separated in different columns. So instead of this output: 

|2981  |  Tom   |   Johnsen   |   random    |   name |

I want this output: 

|2981- Tom Johnsen - random name|

Upvotes: 0

Views: 60

Answers (3)

Fahmi
Fahmi

Reputation: 37473

use concat function

SELECT concat(dbo.Request.RequestId,'- ', dbo.Employee.Givenname, '',dbo.Employee.Surname,'- ', Employee_1.Surname,'', Employee_1.Givenname )
FROM            dbo.Request INNER JOIN
                         dbo.Employee ON dbo.Request.StartedBy_EmployeeId = dbo.Employee.EmployeeId INNER JOIN
                         dbo.Employee AS Employee_1 ON dbo.Request.TargetEmployeeId = Employee_1.EmployeeId
WHERE        (dbo.Request.State = '4')

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use concat() function

    SELECT        concat(dbo.Request.RequestId,'-',
                 dbo.Employee.Givenname,'-',
                dbo.Employee.Surname,'-',
                Employee_1.Surname,'-',
               Employee_1.Givenname )
FROM            dbo.Request INNER JOIN
                         dbo.Employee ON dbo.Request.StartedBy_EmployeeId = dbo.Employee.EmployeeId INNER JOIN
                         dbo.Employee AS Employee_1 ON dbo.Request.TargetEmployeeId = Employee_1.EmployeeId
WHERE        (dbo.Request.State = '4')

Upvotes: 1

LoztInSpace
LoztInSpace

Reputation: 5697

SELECT  dbo.Request.RequestId +'|'+ dbo.Employee.Givenname+'|'+ dbo.Employee.Surname ...etc. FROM
    …

But typically you'd select the columns and do the formatting in the client

Upvotes: 0

Related Questions