Combine the same column from multiple rows into one row

Question

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.

• Biju jose: how your are identifying the D/L number from the two row?
• mathewb: Are you guaranteed to have both an Area 1 and Area 2 value for each record? If not, what do you expect to see as output?
• Craig Whalland: Good point - it turns out there wasn't a guaranteed entry for Area 1 and Area 2 so the solution I used below was missing rows. However - this was due to bad data - so once I fixed that up and ensured there was data in these two areas the simple 2 join solution worked and got me what I needed.

Answer 1

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

• Craig Whalland: This worked great - nice and simple and gave me what I needed. Thanks to everyone for all the quick feedback. Much appreciated.

Answer 2

Took the cte code from @Aduguid, another alternative is

;WITH
hr_table
AS
(
 SELECT tbl.* FROM (VALUES
 ( 'Joe', 'Smith', 456654)
 ) tbl ([First Name], [Last Name], [Employee_ID]) 
)
, 
user_defined_table
AS
(
 SELECT tbl.* FROM (VALUES
 ( 1, 'ABCD1234', '31-Aug-2018', 456654)
 , ( 2, '69090', '23-May-2022', 456654)
 ) tbl ([Area], [ud_1_text], [ud_2_text], [Employee_ID]) 
)
SELECT h.Employee_ID,h.[First Name],h.[Last Name],
MAX(CASE WHEN u.Area=1 THEN u.ud_1_text ELSE '' END) AS [D/L Number],
MAX(CASE WHEN u.Area=1 THEN u.ud_2_text ELSE '' END) AS [expiry],
MAX(CASE WHEN u.Area=2 THEN u.ud_1_text ELSE '' END) AS [First Aid],
MAX(CASE WHEN u.Area=2 THEN u.ud_2_text ELSE '' END) AS [expiry]
 FROM hr_table h
INNER JOIN user_defined_table u
ON h.Employee_ID=u.Employee_ID
GROUP BY h.Employee_ID,h.[First Name],h.[Last Name]

---

• aduguid: Nice work with the SQL
• Biju jose: @aduguid, thanks. I always use this type of static pivot for most cases for a year wise monthly report, another one using row number function to find last three records and pivot them to get single row likewise.

Answer 3

You could use the PIVOT to achieve this.

WITH
hr_table
AS
(
 SELECT tbl.* FROM (VALUES
 ( 'Joe', 'Smith', 456654)
 ) tbl ([First Name], [Last Name], [Employee_ID]) 
)
, 
user_defined_label
AS
(
 SELECT tbl.* FROM (VALUES
 ( 1, 'Drivers Licence')
 , ( 2, 'First Aid Cerification')
 ) tbl ([Area], [Description]) 
)
,
user_defined_table
AS
(
 SELECT tbl.* FROM (VALUES
 ( 1, 'ABCD1234', '31-Aug-2018', 456654)
 , ( 2, '69090', '23-May-2022', 456654)
 ) tbl ([Area], [ud_1_text], [ud_2_text], [Employee_ID]) 
)
, 
user_defined_list
AS
(
 SELECT
 hr.[First Name]
 , hr.[Last Name]
 , hr.[Employee_ID]
 , val.[Area]
 , lbl.[Description]
 , val.[ud_1_text]
 , val.[ud_2_text]
 FROM 
 hr_table AS hr
 INNER JOIN user_defined_table AS val ON hr.[Employee_ID] = val.[Employee_ID]
 INNER JOIN user_defined_label AS lbl ON val.[Area] = lbl.[Area]
)
SELECT 
 dsc.[First Name]
 , dsc.[Last Name]
 , dsc.[Employee_ID]
 , dsc.[Drivers Licence] 
 , [Drivers Licence Expiry] = dte.[Drivers Licence] 
 , dsc.[First Aid Cerification]
 , [First Aid Cerification Expiry] = dte.[First Aid Cerification]
FROM 
 (
 SELECT 
 ud.[First Name]
 , ud.[Last Name]
 , ud.[Employee_ID]
 , ud.[Description]
 , ud.[ud_1_text]
 FROM 
 user_defined_list AS ud
 ) AS ud
 PIVOT
 (
 MAX(ud.[ud_1_text]) 
 FOR ud.[Description] IN ([Drivers Licence], [First Aid Cerification])
 ) AS dsc
 INNER JOIN (
 SELECT 
 ds.[Employee_ID]
 , ds.[Drivers Licence] 
 , ds.[First Aid Cerification]
 FROM 
 (
 SELECT
 ud.[First Name]
 , ud.[Last Name]
 , ud.[Employee_ID]
 , ud.[Description]
 , ud.[ud_2_text]
 FROM 
 user_defined_list AS ud
 ) AS ud
 PIVOT
 (MAX(ud.[ud_2_text]) 
 FOR ud.[Description] IN ([Drivers Licence], [First Aid Cerification])) AS ds 
 ) AS dte ON dte.[Employee_ID] = dsc.[Employee_ID]

screenshot