Your table structure makes the SQL a little inelegant, but I'd say this
would give you what you seem to want:
select Hospital1Code from tab where Hospital1Code is not null
union
select Hospital2Code from tab where Hospital2Code is not null
union
select Hospital3Code from tab where Hospital3Code is not null
The "union" will eliminate duplicates. Maybe this would be better
select * from (
select Hospital1Code from tab where Hospital1Code is not null
union
select Hospital2Code from tab where Hospital2Code is not null
union
select Hospital3Code from tab where Hospital3Code is not null
) A order by 1
Jim
On Sat, Jan 2, 2010 at 6:43 AM, bharani kumar <bharanikumariyer...@gmail.com
> wrote:
> Hi
>
> My fields something like
>
> hospital1,hospital2,hospital3,patientname,
>
>
> Exact table look like
>
> PatientName Hospital1Code Hospital2Code Hospital3Code
>
> Bharani 1234 NULL NULL
>
> Kumar 5678 1234 NULL
>
> Senthil 9632 5675 8524
>
> John 1234 4567 8524
>
>
> Can u tell me the query which return output like ,
>
>
> HospitalID
>
> 1234
> 5678
> 9632
> 5675
> 8524
> 4567
> 8524
>
> Constraint are
>
> 1. No Duplicate records,
> 2.One single column as Output Result ,
>
>
>
> This query purpose is , i have around 1000 patients in my DB,
>
> Each patient may have one,two,three hospital code,that's y the field are
> hospital1,hosptial2,hospital3,
>
>
> i know , i can display all hospital code with unique , but i dont in the
> single column , with unique record,
>
> Can you tell me how to do this ?
>
>
> Thanks
>
--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com
