Sql-server – Combine the same column from multiple rows into one row

join;sql server

I am joining two tables HR and UserDefined based on the Employee_ID:

HR Table

First Name  Last Name   Employee_ID
----------  ---------   -----------
Joe         Smith       456654

User Defined Table

Area    ud_1_text   ud_2_text   Employee_ID
----    ---------   ----------  -----------
1       ABCD1234    31/08/2018  456654
2       69090       23/05/2022  456654

Now I know that if Area equals 1 then it is a "Drivers Licence" and if Area equals 2 that it is a "First Aid Certification".

I would like to be able to return the following in one line per Employee_ID:

Employee_ID First Name  Last name   D/L Number  Expiry      First Aid Expiry
----------- ----------  ---------   ----------  ----------  --------- ----------
456654      Joe         Smith       ABCD1234    31/08/2018  69090     23/05/2022

Any ideas on how to do this?
Microsoft SQL Server query please.

Best Answer

Join to User Defined Table twice, once with Area = 1 and once with Area = 2.

from [HR Table] as HT
  inner join [User Defined Table] as UT1
    on HT.Employee_ID = UT1.Employee_ID and
       UT1.Area = 1
  inner join [User Defined Table] as UT2
    on HT.Employee_ID = UT2.Employee_ID and
       UT2.Area = 2

Get the D/L Number with Expiry from UT1 and First Aid with Expiry from UT2

Related Solutions

Sql-server – Combine column from multiple rows into single row

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Combine column from multiple rows into one row in SQLITE

You can use the GROUP_CONCAT() function:

SELECT a.Name               AS ArticleName
     , GROUP_CONCAT(t.Name) AS TagList
FROM Articles AS a
  LEFT JOIN ArticlesTags AS at
    ON at.ArticleId = a.Id
  LEFT JOIN Tags AS t
    ON t.Id = at.TagId
GROUP BY a.Id, a.Name ;

Best Answer

Related Solutions

Sql-server – Combine column from multiple rows into single row

Combine column from multiple rows into one row in SQLITE

Related Question