Results 1 to 9 of 9

Thread: Select command for multiple instances of same column

  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Select command for multiple instances of same column

    Hi;
    I have a table with the following columns:
    Rpt
    Line
    Col
    Txt

    The table with values looks like this:

    Rpt Line Col Txt
    900 002 1 Main Street
    901 002 1 Sub Street
    900 002 2 #343
    901 002 2 #546


    I need to extract Street (Col with value 1), PO Box (Col with value 2) and the Rpt value
    My output table should be something like this:

    Rpt Street POBox
    900 Main Street #343
    901 Sub Street #546

    How do I form the query.

    Thanks in advance

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    yesmein, Here is one way. There may be others. I don't know what SQL variation you are using but I think all of them can do this (but they may use different functions). This is from DB2:
    Code:
    SELECT Rpt
         , Line
         , MAX(CASE Col WHEN 1 THEN Txt ELSE ' ' END) as Txt1
         , MAX(CASE Col WHEN 2 THEN Txt ELSE ' ' END) as Txt2
    FROM table-name
    GROUP BY Rpt, Line

  3. #3
    Join Date
    Apr 2009
    Posts
    8
    Thank you Das.
    I am using MS SQL Server. Can you please tell me the SQL for it?

  4. #4
    Join Date
    Apr 2009
    Posts
    86
    While I haven't tested it, I believe it will work as it is on SQL Server.

    By the way, I think Access would need the IIF instead of the CASE and I don't have a clue what MySQL or Oracle would use.

  5. #5
    Join Date
    Apr 2009
    Posts
    8
    Hi, there are two conditions:
    one is line number and the other is column number. In the sql command you provided I think you just gave the column. It did not work. Any suggestions?

  6. #6
    Join Date
    Apr 2009
    Posts
    86
    Can you supply a more complete set of example rows? From what I can see:
    Code:
    Rpt Line Col Txt
    900 002  1   Main Street
    901 002  1   Sub Street
    900 002  2   #343
    901 002  2   #546
    
    Rpt Street      POBox
    900 Main Street #343
    901 Sub Street  #546
    You needed to Group Rpt together (900, 901) while displaying Col 1 Txt followed by Col 2 Txt. Since all the Line values are the same (002), I need to know how they relate.

    PS, I just noticed I included LINE in the Select. If that is the problem, just remove Line from the Select list and the Group By.
    Last edited by SDas; 06-20-2009 at 04:05 PM.

  7. #7
    Join Date
    Apr 2009
    Posts
    8
    The way the data is to expand further

    Code:
    Rpt Line Col Txt
    900 002  1   Main Street
    901 002  1   Sub Street
    900 002  2   #343
    901 002  2   #546
    900 003  1   CA
    901 003  1   NV
    900 004  1   90036
    901 004  1   89119
    Basically, the table has information related to the location of an office.
    Eg: Line 002 Col1 refers to Street
    Line 002 Col2 refers to PO Box
    Line 003 Col1 refers to State
    Line 004 Col1 refers to Zipcode

    Since all this information is in the same table and the data in the same column (txt). I want to be able to create a new table like:
    Code:
    rpt   Street  POBox  State  ZipCode
    and populate it with the values from the main table.

    Thanks for your inputs.
    Last edited by yesmein; 06-20-2009 at 04:40 PM.

  8. #8
    Join Date
    Apr 2009
    Posts
    86
    Okay, Try this:
    Code:
    SELECT Rpt
         , MAX(CASE WHEN Line = '0002' and Col = 1 THEN Txt ELSE '' END) AS Street
         , MAX(CASE WHEN Line = '0002' and Col = 2 THEN Txt ELSE '' END) AS POBox
         , MAX(CASE WHEN Line = '0003' and Col = 1 THEN Txt ELSE '' END) AS State
         , MAX(CASE WHEN Line = '0004' and Col = 1 THEN Txt ELSE '' END) AS ZipCode
    FROM table-name
    GROUP BY Rpt
    There is one MAX(CASE ) for each column you are trying to generate. I used the supplied values to populate the WHEN part of the CASE Expression. If you have more or different combinations, it may need to be modified.

    By the way, I believe SQL Server has a Cross table Join that may also work for this. I don't use it so I am not sure of the syntax.

  9. #9
    Join Date
    Apr 2009
    Posts
    8
    Thanks a lot, it worked!!!
    Your Awesome!!

    Just to understand the theory, I always thought Max was for math function. How does is work along with case here (max(case))?

Posting Permissions

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