#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,757
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    6
    Rep Power
    0
    Of course if the Groups can vary you should change the following SQL select statement into a dynamic SQL

    Code:
    ; with cte1 as (
    	select Subjects as "4O1", ROW_NUMBER() over (Order By Groups) as rn
    	from teaching
    	where Groups = '4O1'
    ), cte2 as (
    	select Subjects as "4O2", ROW_NUMBER() over (Order By Groups) as rn
    	from teaching
    	where Groups = '4O2'
    )
    select "4O1", "4O2"
    from cte1
    full outer join cte2 on cte1.rn = cte2.rn
    In SQL Pivot queries we need to use an aggregation function. But for this case I could not figure out a suitable one.

    Here is the output of the above SQL statement fomed of CTEs
    Code:
    4O1                  4O2
    -------------------- --------------------
    Chemistry            Chemistry
    Geography            German
    History              Spanish
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,757
    Rep Power
    4288
    i stand by my advice given a month ago
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo