How to pivot rows into columns (custom pivoting)

by

You could probably do it with the PIVOT function, but I prefer the old school method:

SELECT
    dy,
    MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
    MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,
    MAX(CASE WHEN period = 3 THEN subj ELSE NULL END) AS P3,
    MAX(CASE WHEN period = 4 THEN subj ELSE NULL END) AS P4,
    MAX(CASE WHEN period = 5 THEN subj ELSE NULL END) AS P5,
    MAX(CASE WHEN period = 6 THEN subj ELSE NULL END) AS P6,
    MAX(CASE WHEN period = 7 THEN subj ELSE NULL END) AS P7
FROM
    Classes
GROUP BY
    dy
ORDER BY
    CASE dy
        WHEN 'Mon' THEN 1
        WHEN 'Tue' THEN 2
        WHEN 'Wed' THEN 3
        WHEN 'Thu' THEN 4
        WHEN 'Fri' THEN 5
        WHEN 'Sat' THEN 6
        WHEN 'Sun' THEN 7
        ELSE 8
    END
  • I changed some column names to avoid reserved words

More Related Contents:

  1. SQL Server dynamic PIVOT query?
  2. Simple way to transpose columns and rows in SQL?
  3. T-SQL dynamic pivot
  4. TSQL Pivot without aggregate function
  5. Dynamically create columns sql
  6. TSQL PIVOT MULTIPLE COLUMNS
  7. Dynamic Pivot Columns in SQL Server
  8. Sql PIVOT and string concatenation aggregate
  9. How to create a PivotTable in Transact/SQL?
  10. Pivot data in T-SQL
  11. Using PIVOT in SQL Server 2008
  12. Flattening of a 1 row table into a key-value pair table
  13. Is it possible to write single query on two table which are not connected to each other?
  14. Create a date from day month and year with T-SQL
  15. Creating an index on a table variable
  16. OR is not supported with CASE Statement in SQL Server
  17. Parse comma-separated string to make IN List of strings in the Where clause
  18. SELECT FOR UPDATE with SQL Server
  19. T-SQL: Selecting Column Based on MAX(Other Column)
  20. How to store image in SQL Server database tables column [duplicate]
  21. Procedure or function !!! has too many arguments specified
  22. SQL “between” not inclusive
  23. How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table
  24. Can I set ignore_dup_key on for a primary key?
  25. SQL Server replace, remove all after certain character
  26. Inserting rows into a table with one IDENTITY column only [duplicate]
  27. SQL Row_Number() function in Where Clause
  28. When using GETDATE() in many places, is it better to use a variable?
  29. SQL – How do I get only the numbers after the decimal?
  30. How to retrieve records for last 30 minutes in MS SQL?

Leave a Comment