self joining sql without aggregation

Question

I have a table with the below structure:

    ID   EmployeeType   Name
    1     Contract      John, Baxter
    2     Contract      Will, Smith
    3     Full          Josh, Stevens
    4     Full          Sitar, Zhang

All I need to do is Pivot it so I get the below output:

      Contract_Employee   FullTime_Employee
      John, Baxter        Josh, Stevens
      Will,Smith          Sitar, Zhang

Any idea how I can do this in one query?

What are the relations? Why is John Baxter on the same row as Josh Stevens? — GolezTrol, Jun 19 '15 at 19:04
possible duplicate of [TSQL Pivot without aggregate function](http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Tab Alleman, Jun 19 '15 at 19:05
Thanks for your response. Unfortunately, there is no relation. (Sadly, I just inherited these tables). I just need to list out all the contract employees and full-time employees side-by-side. Without a commonality, I'm pretty sure we cannot do this, but not confident enough to be certain (incase im missing a sql trick that I didn't know before)..hence the question. — Bee, Jun 19 '15 at 19:08
What reporting tool are you using? Can you just put to sub-reports adjacent to each-other? — Brian Pressler, Jun 19 '15 at 19:16
Brian, thanks for your answer. Unfortunately, no tool - just an adhoc query. Yes, I can put 2 lists together..but was hoping to do it in SQL :) — Bee, Jun 19 '15 at 19:30

Andrew · Accepted Answer · 2015-06-19T19:34:42.587

0

That's kind of a funny request. Here's how I would do it: (basically, just deriving a fake key to "join" on for two derived tables, on for contractors, one for employees)

    CREATE TABLE #Table1
        ([ID] int, [EmployeeType] varchar(8), [Name] varchar(13))
    ;

    INSERT INTO #Table1
        ([ID], [EmployeeType], [Name])
    VALUES
        (1, 'Contract', 'John, Baxter'),
        (2, 'Contract', 'Will, Smith'),
        (3, 'Full', 'Josh, Stevens'),
        (4, 'Full', 'Sitar, Zhang'),
        (5, 'Full','Bob, Bob'),
        (6, 'Contract','Bob, Bob')
    ;


    select
    c.name as ContractEmployee,
    f.name as FullTime_Employee
    from
    (
    select
    row_number() over (order by id) as RN,
    name
    from
    #table1
    where 
    employeetype = 'Contract'
      ) c
    full join (
    select
    row_number() over (order by id) as RN,
    name
    from
    #table1
    where 
    employeetype = 'Full'
    ) f
    on 
c.name = f.name OR
c.rn = f.rn

edited Jun 19 '15 at 19:34

answered Jun 19 '15 at 19:16

Andrew

8,445
3
28
46

Thank you this works for the structure as is and for all cases except when there is an employee under both contract and full. I guess I'll have to apply some sort of matching to make sure that it lists out correctly. – Bee Jun 19 '15 at 19:29
If you make your join `c.name = f.name OR c.rn = f.rn`, that will line them up together, if that's what you mean. – Andrew Jun 19 '15 at 19:33
Thanks Andrew...that's what I did and matches most, but ofcourse the data is inconsistent...will have to use regex to find an exact match :) – Bee Jun 19 '15 at 19:57

score 0 · Answer 2 · answered Jun 19 '15 at 19:27

One method of doing this is to use aggregation:

select max(case when employeetype = 'Contract' then Name end) as ContractEmployees,
       max(case when employeetype = 'Full' then Name end) as FullEmployees       
from (select t.*,
             row_number() over (partition by employeetype order by id) as seqnum
      from table t
     ) t
group by seqnum;

self joining sql without aggregation

2 Answers2