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

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    Query to display data a different way


    I have a table with three fields. This table (confirmation) contains up to 4 records for each student noting what documents they authorized (code) and a timestamp (date).

    So confirmation table data looks like:
    StudentID,Code,Date
    123,A,2012-05-01
    123,B,2012-06-12
    123,C,2012-06-13
    123,D,2012-07-12

    What I would like to accomplish is create a query to display the information in this way (first row would be column names, one line per student):
    StudentID,CodeA,CodeB,CodeC,CodeD
    123,2012-05-01,2012-06-12,2012-06-13,2012-07-12

    Any help would be greatly appreciated, since I am still learning SQL. In case you need to know, we use MS-SQL. I apologize if I am not using the correct terminology. Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Code:
    SELECT StudentID
         , MAX(CASE WHEN Code = 'A' 
                    THEN [Date]
                    ELSE NULL END) AS CodeA
         , MAX(CASE WHEN Code = 'B' 
                    THEN [Date]
                    ELSE NULL END) AS CodeB
         , MAX(CASE WHEN Code = 'C' 
                    THEN [Date]
                    ELSE NULL END) AS CodeC
         , MAX(CASE WHEN Code = 'D' 
                    THEN [Date]
                    ELSE NULL END) AS CodeD
      FROM confirmation
    GROUP
        BY StudentID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Thanks that worked perfectly! Now I just need to format the date as mm/dd/yy hh:mm pm. Is there a easy trick to do that?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by kpraeger
    Thanks that worked perfectly! Now I just need to format the date as mm/dd/yy hh:mm pm. Is there a easy trick to do that?
    1. do it in your application layer (best practice)

    or

    2. pick style 100 from http://msdn.microsoft.com/en-us/library/ms187928.aspx (although that uses Jul instead of 07, so you could use 12 nested REPLACE functions)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Thanks. Do you have a suggestion on a good application layer? I simply am providing a spreadsheet type list that can be printed.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by kpraeger
    Do you have a suggestion on a good application layer?
    nope, sorry, i'm not a programmer

    and anyhow, what the heck is wrong with printing 2012-07-26?

    i don't think ~anybody~ will get confused by that

    however, if you print 03/04/2012, many people will take it the wrong way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    nope, sorry, i'm not a programmer

    and anyhow, what the heck is wrong with printing 2012-07-26?

    i don't think ~anybody~ will get confused by that

    however, if you print 03/04/2012, many people will take it the wrong way
    I'm fine with 2012-07-26, but I just don't want 2012-07-26 10:29:38.130 which is how it looks when I run the query. I'd rather just have 2012-07-26 10:29 AM, or something similar.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by kpraeger
    ... or something similar.
    use style 100, then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo