Transforming rows into columns

  • Hi Everyone,

    I have a table with the structure as attached in the Table Structure file.

    Student Id     CourseNumber   ClassNumber InstructionID  InstructorDept Grade Pass/Fail

    But there are multiple students taking different or same courses with the same instructor or different instructor from different departments. I want to transform the rows into column at the student level. i.e., If a student took three courses with the same instructor and same class number as he was failed in the first two attempts and took the third one and passed, I want the rows to be transformed as coursenumber-1 course number-2 coursenumber-3 coursenumber-4 coursenumber-5 coursenumber-6 classnumber-1 classmnumber-2 classmnumber-3 classmnumber-4 classmnumber-5 classmnumber-6 InstructionID-1 InstructionID-2 InstructionID-3 InstructionID-4 InstructionID-5 InstructionID-6 InstructionDept-1 InstructionDept-2 InstructionDept-3 InstructionDept-4 InstructionDept-5 InstructionDept-6 Grade-1 Grade-2 Grade-3 Grade-4 Grade-5 Grade-6 Pass/Fail-1 Pass/Fail-2 Pass/Fail-3 Pass/Fail-4 Pass/Fail-5 Pass/Fail-6. I am aware of using a pivot here but here one student may take the same or different course once twice thrice or six times. If this is confusing I am attaching the required output for me. Any help is highly appreciated.

    PS: As I am not able to save the desired output(after transformation) into one picture I saved them into three. These three pictures come side by side as columns one after the other.

    Thanks,

    RK

    Attachments:
    You must be logged in to view attached files.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Maybe read Jeff Moden's article on pivoting in T-SQL. Otherwise, could you post a CREATE TABLE script and an INSERT script to show some sample data?

    Sounds like you need a PIVOT

  • The sample data is the one that I have posted. And the DDL is not actually implies student ID. I had a requirement from my manager asking for same kind of transforming. So I adopted the data and recreated using student data. Here What I need is pivot but that doesn't solve my issue as it involves dynamic pivot. Different rows had different count and when transforming gives to different number of columns. For Example student Id 4 has two course numbers. When transformed it gives to two columns where as student id 5 has six course number and when transformed it gives to 6 columns. So I want a query which fits this requirement. Hope this clarifies the requirement.

  • What if you used ROWNUMBER() OVER (PARTITION BY StudentID) and then pivoted on that?

  • rkarr7 wrote:

    The sample data is the one that I have posted. And the DDL is not actually implies student ID. I had a requirement from my manager asking for same kind of transforming. So I adopted the data and recreated using student data. Here What I need is pivot but that doesn't solve my issue as it involves dynamic pivot. Different rows had different count and when transforming gives to different number of columns. For Example student Id 4 has two course numbers. When transformed it gives to two columns where as student id 5 has six course number and when transformed it gives to 6 columns. So I want a query which fits this requirement. Hope this clarifies the requirement.

    SQL Server strictly enforces 1st Normal Form.  The upshot of this is that you CANNOT have rows with different numbers of columns, because that violates 1st Normal Form.

    The best that you can do is use a dynamic pivot (or cross tab).  Search this site for the article about dynamic pivots.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks Allen for the reply. The resultant table that I posted is joined with four other tables. The tables that are joined are in 1NF but after joining due to the data we are having multiple rows for each record. For example consider student id 4 where he took the same course twice. The reason is he failed in the first attempt and took the course in the second attempt. So it would definitely asks for multiple rows.

  • How can I use a aggregate function to pivot the data..That's the major concern over here

  • USE tempdb
    DROP TABLE IF EXISTS #;
    CREATE TABLE #(StudentID varchar(12) NOT null
    ,CourseNumber varchar(12) NOT null
    ,ClassNumber varchar(12) NOT null
    ,InstructorID varchar(12) NOT NULL
    ,InstructorDept varchar(12) NOT null
    ,Grade varchar(12) NOT null
    ,PassFail varchar(12) NOT NULL)

    INSERT # VALUES
    (1,185505,2071579,10330352,'CS','A','P')
    ,(2,185505,662146,10049279,'IT','F','F')
    ,(2,185505,662146,10049279,'IT','A','P')
    ,(3,185505,2062816,10327839,'EC','F','F')
    ,(3,3400745,2062816,10327839,'EE','D','P')
    ,(4,186020,186025,10014141,'DS','F','F')
    ,(4,186020,186025,10014141,'DS','3','P')
    ,(5,457723,186071,10014169,'PA','F','F')
    ,(5,457723,186071,10014169,'PA','E','F')
    ,(5,457723,186071,10014169,'PA','C','P')
    ,(5,186069,186071,10014169,'PS','F','F')
    ,(5,186069,186071,10014169,'PS','A','P')
    ,(5,186069,186071,10014169,'PIS','E','F');

    WITH cte
    AS (SELECT StudentID,
    CAST(CourseNumber AS varchar(12)) CourseNumber,
    CAST(ClassNumber AS varchar(12)) AS ClassNumber,
    CAST(InstructorID AS varchar(12)) AS InstructorID,
    Grade,
    PassFail,
    CAST(ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY CourseNumber, ClassNumber) AS varchar(12)) row_num
    FROM #),
    cte2
    AS (SELECT StudentID,
    val,
    CONCAT(col, '_', row_num) new_col
    FROM (SELECT * FROM cte) a
    UNPIVOT (val
    FOR col IN (CourseNumber, ClassNumber, InstructorID, Grade, PassFail)) b)
    SELECT StudentID,
    ClassNumber_1,
    ClassNumber_2,
    ClassNumber_3,
    ClassNumber_4,
    ClassNumber_5,
    ClassNumber_6,
    CourseNumber_1,
    CourseNumber_2,
    CourseNumber_3,
    CourseNumber_4,
    CourseNumber_5,
    CourseNumber_6,
    Grade_1,
    Grade_2,
    Grade_3,
    Grade_4,
    Grade_5,
    Grade_6,
    InstructorID_1,
    InstructorID_2,
    InstructorID_3,
    InstructorID_4,
    InstructorID_5,
    InstructorID_6,
    PassFail_1,
    PassFail_2,
    PassFail_3,
    PassFail_4,
    PassFail_5,
    PassFail_6
    FROM (SELECT * FROM cte2) a
    PIVOT (MAX(val)
    FOR new_col IN (ClassNumber_1, ClassNumber_2, ClassNumber_3, ClassNumber_4, ClassNumber_5, ClassNumber_6,
    CourseNumber_1, CourseNumber_2, CourseNumber_3, CourseNumber_4, CourseNumber_5, CourseNumber_6,
    Grade_1, Grade_2, Grade_3, Grade_4, Grade_5, Grade_6, InstructorID_1, InstructorID_2,
    InstructorID_3, InstructorID_4, InstructorID_5, InstructorID_6, PassFail_1, PassFail_2,
    PassFail_3, PassFail_4, PassFail_5, PassFail_6)) b;

    That's the best example I can come up with. You'd have to UNPIVOT the columns you want to appear as headings, CONCAT them with the row number, then PIVOT on that CONCAT'd value. It looks unwieldy and it is, SQL is not the best solution for this kind of output, better to do it in a report product like SSRS or an Excel pivot table. Change the table/column names and types to match yours. You'll need to convert all non-character data types to compatible ones if you're going to PIVOT on character data.

  • Thanks Rob for the solution. It is working for this data. I will try to replicate the same for my actual data. I will use SSRS to get the things done more efficiently.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply