How can I 'transpose' my data using SQL and remove duplicates at the same time?

Question

I have the following data structure in my database:

LastName    FirstName    CourseName
John        Day          Pricing
John        Day          Marketing
John        Day          Finance
Lisa        Smith        Marketing
Lisa        Smith        Finance
etc...

The data shows employess within a business and which courses they have shown a preference to attend. The number of courses per employee will vary (i.e. as above, John has 3 courses and Lisa 2).

I need to take this data from the database and pass it to a webpage view (asp.net mvc).

I would like the data that comes out of my database to match the view as much as possible and want to transform the data using SQl so that it looks like the following:

LastName    FirstName    Course1    Course2    Course3
John        Day          Pricing    Marketing  Finance
Lisa        Smith        Marketing  Finance

Any thoughts on how this may be achieved?

---

Note: one of the reasons I am trying this approach is that the original data structure does not easily lend itself to be iterated over using the typical mvc syntax:

<% foreach (var item in Model.courseData) { %>

Because of the duplication of names in the orignal data I would end up with lots of conditionals in my View which I would like to avoid.

I have tried transforming the data using c# in my ViewModel but have found it tough going and feel that I could lighten the workload by leveraging SQL before I return the data.

Thanks.

Answer 1

You could use aggregate string concatenation with eg. ',' as separator and have results more like:

LastName    FirstName    Courses
John        Day          Pricing, Marketing, Finance
Lisa        Smith        Marketing, Finance

Answer 2

You can transpose the data using PIVOT, but you will get the courses as column names, and a 1 or 0 as data for attending / not attending. Using that and a bit of code in the ViewModel should get you what you want:

SELECT  * FROM 
(SELECT FirstName, LastName, Course FROM Courses) src
PIVOT (COUNT(Course) FOR Course IN ([Finance] ,[Marketing],[Pricing])) AS pvt

Which gives you:

FirstName    LastName  Finance Marketing Pricing
--------------------------------------------------------------
John         Day       1       1         1
Lisa         Smith     1       1         0

Answer 3

Before you begin the presentation layer, you may want to re-examine your data layer.

I would normalize your data into three tables. One that contains people called Person (with some primary key - your call) and one that contains courses called Course (again, with some form of primary key). Then, you can handle your mappings in a third table (call it Schedule) that relates the primary key of Person to the primary key of Course.

If I can get to a spot where I can whip out an ER diagram, I'll post on an edit.

Once you have your data in order, it makes the task of displaying and arranging it much easier.

Answer 4

There is nothing in your sample data to indicate priority. E.g., how do we know that Day John's first choice is Pricing? Presuming you had such a column, then you can do something like:

Select LastName, FirstName
    , Min( Case When Priority = 1 Then CourseName End ) As Course1
    , Min( Case When Priority = 2 Then CourseName End ) As Course2  
    , Min( Case When Priority = 3 Then CourseName End ) As Course3
From Table
Group By LastName, FirstName

Answer 5

You can do this in straight SQL (runnable example):

DECLARE @data AS TABLE (LastName varchar(25), FirstName varchar(25), CourseName varchar(25))

INSERT INTO @data VALUES ('John', 'Day', 'Pricing')
INSERT INTO @data VALUES ('John', 'Day', 'Marketing')
INSERT INTO @data VALUES ('John', 'Day', 'Finance')
INSERT INTO @data VALUES ('Lisa', 'Smith', 'Marketing')
INSERT INTO @data VALUES ('Lisa', 'Smith', 'Finance')

SELECT *
FROM (
SELECT LastName, FirstName, CourseName, Bucket = 'Course' + CAST(ROW_NUMBER() OVER(PARTITION BY LastName, FirstName ORDER BY CourseName) AS varchar) FROM @data
) AS n PIVOT (MIN(CourseName) FOR Bucket IN ([Course1], [Course2], [Course3])) AS pvt