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

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开发者_Python百科 data.

Thanks.

---

NOTE: I don't want to use the Database PIVOT feature because the number of classes will most likely change and you have to update your SQL query.

What you are trying to do should not be done in the View.
The Model object should be simple enough to be rendered when passed to a View.
Because, the View shouldn't have any complicated logic in it.

Therefore, we need to pre-process the data returned from the SQL Server Database first.

I am going to assume that you have an object like this because you didn't mention it has a unique identifier like "CustomerId" or something.

public class CourseData {
    public string FirstName { get; set; }
    public string LastName  { get; set; }
    public string ClassName { get; set; }
}

This is going to be my [Model] object.

public class MyViewModel {
    public IDictionary<string, IList<string>> CourseData { get; set; }
    public int MaxCourseCount { get; set; }
}

This is my Action method in the Controller.

public ActionResult MyView()
{
    IDictionary<string, IList<string>> dict = new Dictionary<string, IList<string>>();

    // retrieve data from the database
    IList<CourseData> result = RetrieveData();
    foreach (var item in result)
    {
        // [FirstName] and [LastName] combo will be used as KEY entry
        string key = item.FirstName + " " + item.LastName;

        if (dict.ContainsKey(key))
        {
            // add the class name into an existing "string" collection
            dict[key].Add( item.ClassName );
        }
        else
        {
            // instantiate a new "string" collection and add the class name.
            dict[key] = new List<string> { item.ClassName };
        }
    }

    // find out which Person has attended the most number of classes.
    int maxCourseCount = 0;
    foreach (var key in dict.Keys)
    {
        int valueCount = dict[key].Count;
        if (valueCount > maxCourseCount)
            maxCourseCount = valueCount;
    }


    MyViewModel model = new MyViewModel {
        CourseData = dict,
        MaxCourseCount = maxCourseCount
    };
    return View(model);
}

I aggregated the data into a Data Structure that is easier to render. I intentionally added [MaxCourseCount] property to my Model object because it seems like you want to render the Course Data in a <table> format.

So, all you need to do now is

1. loop through your Model.CourseData dictionary object's Keys.
2. render the Course Names in individual <td> table cells.
3. render remaining <td> table cells based on your Model's [MaxCourseCount] value.

I hope this helps.

-Soe

---

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

---

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.

---

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

---

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

---

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