Select command for multiple instances of same column

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