#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0

    SQL Server 2008 R2 - Transposing rows and colums


    Hello

    I have a requirement to transpose rows to columns on a simple table.
    A sample of the present output looks like this (apologies, I can't get the "table" formatting to work) :

    Code:
        Groups    Subjects
        ------    ----------
        4O1	      Chemistry
        4O1	      Geography
        4O1	      History
        4O2	      Chemistry
        4O2	      German
        4O2	      Spanish
    ... and I'm trying to display it like this :

    Code:
        401         402
        ---------   ---------
        Chemistry   Chemistry  
        Geography   German
        History     Spanish
    The table is called "teaching" , and it has only two fields, "Groups" and "Subjects".

    Now, looking around on the web, I've seen plenty of examples where PIVOT , ROW_NUMBER() OVER(PARTITION and MAX are
    used, but is seems these can only be used when the amount of required columns is fixed, and can be hard-coded.

    In this case, (see the first output), the "Groups" can vary (they can change, or more can be added, depending on circumstances
    outside of my control), so the code would need to be able to handle this.

    If anyone wants to have a go, here is the SQL to create and populate the
    recordset :


    Code:
    BEGIN
    
        IF OBJECT_ID('teaching') IS NOT NULL DROP TABLE teaching
    
        CREATE TABLE teaching
        (Groups   NVARCHAR(20) NOT NULL,
         Subjects NVARCHAR(20) NOT NULL)
     
        INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'Chemistry')
        INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'Geography')
        INSERT INTO teaching (Groups, Subjects) VALUES ('4O1', 'History')
        INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'Chemistry')
        INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'German')
        INSERT INTO teaching (Groups, Subjects) VALUES ('4O2', 'Spanish')  
     
        END
    Thanks in advance!

    Jim
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,665
    Rep Power
    4288
    short answer: no

    longer answer: do cosmetic re-formatting in the application layer (php or whatever)... keep sql simple and performant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo