Results 1 to 5 of 5

Thread: Rows to Colums

  1. #1
    Join Date
    Jun 2003
    Posts
    34

    Unanswered: Rows to Colums

    Hi I have a problem. Can anyone help me write this SQL ?
    I have a table as :

    Col Type pri_flag net_flag pct_flag
    D 0 0 0
    M 1 0 0
    M 0 1 1
    M 1 1 0
    M 1 0 1

    Now I want a output like:
    0 - D (Everytime its 'D')
    1 - M
    1 - pri flag
    1 - M
    1 - net_flag
    0 - pct_flag
    1 - M
    1 - pri_flag
    1 - net_flag
    1 - M
    1 - pri_flag
    0 - pct_flag

    The logic is:
    i) If we fing Col Type = D in a record put 0
    ii) If we get Col Type = M then put 1 for M for that record
    iii) If we get a pri_flag=1 along with M we put 1
    iv) If we get a net_flag=1 along with M we put 1
    v) If we get a pct_flag=1 along with M we put 0

    Hope I could explain it.
    Thanks

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Talking Result

    Hello,

    use this

    SELECT m.*,
    DECODE(type, 'D', 0, 'M', DECODE(pct_flag, 1, 0, 1)) AS result
    FROM mysql m

    (mysql is the table i have used)


    Hope that helps ?

    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

  3. #3
    Join Date
    Jun 2003
    Posts
    34

    HI

    I don't think I'll get the desired result by that SQL. Please see the result that I have given.

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    That gives exacly what you have ask for

    Hello,

    these are the rules you have defined:

    The logic is:
    i) If we fing Col Type = D in a record put 0
    ii) If we get Col Type = M then put 1 for M for that record
    iii) If we get a pri_flag=1 along with M we put 1
    iv) If we get a net_flag=1 along with M we put 1
    v) If we get a pct_flag=1 along with M we put 0

    the SQL statement

    SELECT m.*,
    DECODE(type, 'D', 0, 'M', DECODE(pct_flag, 1, 0, 1)) AS result
    FROM mysql m

    1) the statement print a 0 when type is D ....
    2) the statement print a 1 when type is M ... but you have also defined in
    5 that when pct_flag = 1 then print 0 ... the statement does ...
    3) pri_flag = 1 and type = M the print a 1 ... this is waste, cause you want to print always a 1 when type = M
    (same in 4)

    So, where is the mistake ?

    Best regards
    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

  5. #5
    Join Date
    Jun 2003
    Posts
    34
    According to the output I have shown I should get something like :

    output
    -----
    0
    1
    1
    1
    1
    0
    1
    1
    1
    1
    1
    0

    Analyse each row. You'll see that :
    Row 1: Type = D so the first record in the output is 0
    Row 2: Type = M and pri_flag = 1 so we get two consequitive 1's
    Row 3: Type = M , pri_flag = 1 and pct_flag =1 so we get 1 for M, another 1 for pri_flag and a 0 for pct_flag as it's set to 1.
    so on and so forth.

    If you see the output closely you have an output of 12 records and NOT
    5. which your sql will generate.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •