Rotate columns to rows for joined tables

Question

I have two tables similar to shown below (just leaving out fields for simplicity).

Table lead :

 id  |  fname    |   lname    |     email
 ---------------------------------------------
 1   |  John     |   Doe      |    [email protected]
 2   |  Mike     |   Johnson  |    [email protected]

Table leadcustom :

 id   |   leadid     |    name         |    value
 -------------------------------------------------
 1    |   1          |    utm_medium   |    cpc
 2    |   1          |    utm_term     |    fall
 3    |   1          |    subject      |    business
 4    |   2          |    utm_medium   |    display
 5    |   2          |    utm_term     |    summer
 6    |   2          |    month        |    may
 7    |   2          |    color        |    red

I have a database that captures leads for a wide variety of forms that often have many different form fields. The first table gets the basic info that I know is on each form. The second table captures all other forms fields that were sent over so it can really contain a lot of different fields.

What I am trying to do is to do a join where I can grab all fields from lead table along with utm_medium and utm_term from leadcustom table. I don't need any additional fields even if they were sent over.

Desired results :

 id    |  fname    |    lname   |   email       |  utm_medium   |  utm_term
 ---------------------------------------------------------------------------
 1     |  John     |    Doe     |   [email protected] |  cpc          |  fall
 2     |  Mike     |    Johnson |   [email protected] |  display      |  summer

Only way I know I could do this is to grab all lead data and then for each record make more calls to get leadcustom data I am looking for but I know there has to me a more efficient way of getting this data.

I appreciate any help with this and it is not something I can change the way I capture that data and table formats.

Answer 1

If your columns are fixed, you can do this with group by + case + max like this:

select
  fname,
  lname,
  email,
  max(case when name = 'utm_medium' then value end) as utm_medium,
  max(case when name = 'utm_term' then value end) as utm_term
from
  lead l
  join leadcustom c
    on l.id = c.leadid
group by
  fname,
  lname,
  email

The case will assign value from the leadcustom table when it matches the given name, otherwise it will return null, and max will pick take the assigned value if it exists over the null.

You can test this in SQL Fiddle

The other way to do this is to use pivot operator, but that syntax is slightly more complex -- or at least this is more easy for me.

Answer 2

You can try with pivot and join:

select [id]
     , [fname]
     , [lname]
     , [email]
     , [utm_medium]
     , [utm_term]
from ( select t2.*
            , t1.[name]
            , t1.[value]
       from [leadcustom] t1
       join [lead] t2 on t2.[id] = t1.[leadid]
) t
pivot (
       max([value])
       for [name] in ([utm_medium], [utm_term])
) pt

pivot rotates the joined table-valued expression, by turning the unique values from [value] column in the expression into [utm_medium] and [utm_term] columns in the output, and performs fake aggregation with max function (it works so because a corresponding column can have multiple values for one unique pivoted column, in this case, [name] for [value]).

SQLFiddle

Answer 3

You can use a cte or a derived table to solve this:

cte:

;with cte as 
(
    select leadid, [name], [value]
    from leadcustom
    where name in('utm_medium', 'display')
)

select id, fname, lname, email, [name], [value]
from lead 
inner join cte on(id = leadid)

Derived table:

select id, fname, lname, email, [name], [value]
from lead 
inner join
(
    select leadid, [name], [value]
    from leadcustom
    where name in('utm_medium', 'display')
) derived on(id = leadid)

and since suslov used JamesZ's fiddle, I will use it too...

Answer 4

Unless I interpret your question incorrectly - in which case I'm happy to be corrected - you could achieve your goal with a simple left join where you join on ID of the first table:

select ld.*, ldcust.utm_medium, ldcust.utm_term 
from lead ld
left join leadcustom ldcust 
on ld.id = ldcust.leadid

Answer 5

declare @t table (Id int,fname varchar(10),lname varchar(10),email varchar(20))
insert into @t(Id,fname,lname,email)values (1,'john','doe','[email protected]'),(2,'mike','johnson','[email protected]')
declare @tt table (id int,leadid int,name varchar(10),value varchar(10))
insert into @tt(id,leadid,name,value)values
(1,1,'utm_medium','cpc'),
(2,1,'utm_term','fall'),
(3,1,'subject','business'),
(4,2,'utm_medium','display'),
(5,2,'utm_term','summer'),
(6,2,'month','may'),(7,2,'color','red')
select Id,fname,lname,
email,
[utm_medium],
[utm_term] 
from (
select t.Id,
t.fname,
t.lname,
t.email,
tt.name,
tt.value
   from @t t JOIN @tt tt
   ON t.Id = tt.leadid)R
PIVOT(MAX(value) for name IN([utm_medium],[utm_term]))P