2

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.

  • how your are identifying the D/L number from the two row? – Biju jose Aug 16 '18 at 4:49
  • 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? – mathewb Aug 16 '18 at 16:10
  • 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. – Craig Whalland Aug 17 '18 at 6:48
2

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

  • 1
    This worked great - nice and simple and gave me what I needed. Thanks to everyone for all the quick feedback. Much appreciated. – Craig Whalland Aug 17 '18 at 2:32
3

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]

  • Nice work with the SQL – aduguid Aug 16 '18 at 14:20
  • @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. – Biju jose Aug 16 '18 at 20:36
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.