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

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

---

Thank you Das.
I am using MS SQL Server. Can you please tell me the SQL for it?

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.

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?

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.

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.

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.

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))?