Sql Server Pivot with lookup table

Question

I've looked through most of the pivot table examples, but I haven't found quite the same scenario yet (plus, I'm probably out of my league with this).

I have an Employees table, an Attributes table, and a lookup table that matches employees and attributes:

dbo.Employees
employee_ID INT
employee_name NVARCHAR(50)

employee_ID | employee_name 
1           | Joe Smith
2           | Don Johnson

dbo.Attributes
attribute_ID INT
attribute_description NVARCHAR(50)

attribute_ID | attribute_description
50           | Works Weekends
55           | Wears Hats
61           | Enjoys Baseball

dbo.EmployeeAttributes
employee_attribute_ID INT
employee_ID_fk INT
attribute_ID_fk INT

employee_attribute_ID | employee_ID_fk | attribute_ID_fk
20                    | 1              | 50
21                    | 2              | 61
22                    | 2              | 55

I'm trying to end up with a result set where the attributes are displayed as columns, with boolean values indicating whether the attribute is present for a given employee, like:

employee_ID | employee_name | Works Weekends | Wears Hats | Enjoys Baseball
1           | Joe Smith     | 1              | 0          | 1
2           | Don Johnson   | 0              | 1          | 0

Any help would be very much appreciated.

i've got a blog on this subject [here](http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/how-to-unpivot-and-pivot). let me know if that helps. — DForck42, Sep 01 '11 at 18:21

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

I'm kind of out of time right now, but couldn't help giving you some pointers.:

Assemble your basic (non pivot) query. You should use a couple of LEFT JOIN between your tables. Once you have that, proceed to Pivoting.
Look at the stuff I've written on Dynamic PIVOT:

edited May 23 '17 at 12:29

Community

1
1

answered Sep 01 '11 at 16:40

Adriano Carneiro

57,693
12
90
123

Thanks Adrian - this put me on the right track. Any ideas how to add a case statement to change nulls to 0 and values to 1? – Eyeball Sep 02 '11 at 15:19
Yes: `case when YourValue is null then 0 else 1 end` – Adriano Carneiro Sep 02 '11 at 15:21
Sorry, I meant 'where' to add the case statement. If I add it to the select statement, it doesn't catch the nulls. – Eyeball Sep 02 '11 at 15:31

Sql Server Pivot with lookup table

1 Answers1