# Miscellaneous > Structured Query Language (SQL) >  Select command for multiple instances of same column

## yesmein

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

----------


## SDas

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:


```
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
```

----------


## yesmein

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

----------


## SDas

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.

----------


## yesmein

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?

----------


## SDas

Can you supply a more complete set of example rows?  From what I can see:


```
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.

----------


## yesmein

The way the data is to expand further



```
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:


```
rpt   Street  POBox  State  ZipCode
```

and populate it with the values from the main table.

Thanks for your inputs.

----------


## SDas

Okay, Try this:


```
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.

----------


## yesmein

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

----------

