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

    Join Date
    Jan 2011
    Posts
    11
    Rep Power
    0

    Question SQL Table to Data Report


    Is it possible to covert the following data,

    COL1.......COL2
    TEST1.....A
    TEST1.....B
    TEST1.....C
    TEST2.....B
    TEST2.....C
    TEST3.....A
    TEST3.....B

    To,

    ......TEST1.....TEST2.....TEST3
    A.....Y...........N............Y
    B.....Y...........Y............Y
    C.....Y...........Y............N

    I tried few things. All ended up in a mess

    Any Suggestions???
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT col2
         , MAX(CASE WHEN col1 = 'TEST1'
                    THEN 'Y'
                    ELSE 'N' END) AS test1
         , MAX(CASE WHEN col1 = 'TEST2'
                    THEN 'Y'
                    ELSE 'N' END) AS test2
         , MAX(CASE WHEN col1 = 'TEST3'
                    THEN 'Y'
                    ELSE 'N' END) AS test3
      FROM daTable
    GROUP
        BY col2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    11
    Rep Power
    0
    Thanx...It Works

    can we re-write this query for dynamic data?

    for ex,

    COL1.......COL2
    TEST1.....A
    TEST1.....B
    TEST1.....C
    TEST2.....B
    TEST2.....C
    TEST3.....A
    TEST3.....B
    .
    .
    .
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    yes, this is done with PIVOT

    there are lots of examples on the internet

    for example, http://www.kodyaz.com/articles/t-sql...-examples.aspx
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    11
    Rep Power
    0
    Ok. Thanx.

IMN logo majestic logo threadwatch logo seochat tools logo