Combining information in two columns

I may be missing something obvious here (in fact it's probably a really
simple problem, and it's probably me being silly - it is Friday after all),
but I can't work out how to do this.
I have a query that performs a full join on two tables, and pulls out four
columns - two containing years, and the others two containing months for
those year. I want to create a master view/query that gives me all the
years, and months in those years once only.
So for example say I have:
Topic_Years, School_Years, Topic_Months, School_Months
Null 1992 Null 10
2004 Null 6
Null
2004 Null 9
Null
2004 Null 10 Null
2004 2004 1 1
2004 2004 2 2
I want to get:
Years, Months
1992 10
2004 1
2004 2
2004 6
2004 9
2004 10
Can somebody point me in the right direction on how to do this?
Cheers!
SarahHi Sara, try this:
SELECT
COALESCE(Topic_Years, School_Years) AS y,
COALESCE(Topic_Months, School_Months) AS m
FROM ...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
news:OpZaUU7KFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I may be missing something obvious here (in fact it's probably a really
>simple problem, and it's probably me being silly - it is Friday after all),
>but I can't work out how to do this.
> I have a query that performs a full join on two tables, and pulls out four
> columns - two containing years, and the others two containing months for
> those year. I want to create a master view/query that gives me all the
> years, and months in those years once only.
> So for example say I have:
> Topic_Years, School_Years, Topic_Months, School_Months
> Null 1992 Null 10
> 2004 Null 6 Null
> 2004 Null 9 Null
> 2004 Null 10
> Null
> 2004 2004 1 1
> 2004 2004 2 2
> I want to get:
> Years, Months
> 1992 10
> 2004 1
> 2004 2
> 2004 6
> 2004 9
> 2004 10
> Can somebody point me in the right direction on how to do this?
> Cheers!
> Sarah
>|||Hi
If you mean first of two year which is not null and first of two month which
is not null then
please try this
select coalesce(topic_years,school_years) as years ,
coalesce(topic_months,school_months) as months from Table
Thanks
AM
"Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
news:OpZaUU7KFHA.1308@.TK2MSFTNGP15.phx.gbl...
> I may be missing something obvious here (in fact it's probably a really
> simple problem, and it's probably me being silly - it is Friday after
all),
> but I can't work out how to do this.
> I have a query that performs a full join on two tables, and pulls out four
> columns - two containing years, and the others two containing months for
> those year. I want to create a master view/query that gives me all the
> years, and months in those years once only.
> So for example say I have:
> Topic_Years, School_Years, Topic_Months, School_Months
> Null 1992 Null 10
> 2004 Null 6
> Null
> 2004 Null 9
> Null
> 2004 Null 10
Null
> 2004 2004 1 1
> 2004 2004 2 2
> I want to get:
> Years, Months
> 1992 10
> 2004 1
> 2004 2
> 2004 6
> 2004 9
> 2004 10
> Can somebody point me in the right direction on how to do this?
> Cheers!
> Sarah
>|||Brilliant, cheers! A little play with my syntax, and it fits into my
existing query.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OsvHNa7KFHA.3132@.TK2MSFTNGP12.phx.gbl...
> Hi Sara, try this:
> SELECT
> COALESCE(Topic_Years, School_Years) AS y,
> COALESCE(Topic_Months, School_Months) AS m
> FROM ...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
> news:OpZaUU7KFHA.1308@.TK2MSFTNGP15.phx.gbl...
>|||"Sarah Clough" <sarah_c_clough@.hotmail.com> wrote in message
news:uz2Ank7KFHA.2804@.TK2MSFTNGP10.phx.gbl...
> Brilliant, cheers! A little play with my syntax, and it fits into my
> existing query.
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
message
> news:OsvHNa7KFHA.3132@.TK2MSFTNGP12.phx.gbl...
If the Years and Months in each Row are not necessarily the Same or Null
then you need
Select Topic_Years, Topic_Months From ...
UNION
Select School_Years, School_Months From ...
Regards,
Jim

combining fields (sort of)

Hi,
Hope this is neither too obvious or laughably impossible, else I'll
just have to be embarrased.
Is there any way to do this:

SELECT field1 if it is not NULL, else SELECT field2..??

The way this dbase is set up, I'm just about suicidal.
THANKS for any help!Use the COALESCE function (or NVL if using Oracle):

COALESCE(field1,field2)|||Originally posted by andrewst
Use the COALESCE function (or NVL if using Oracle):

COALESCE(field1,field2)

NVL did it! THANKS SO MUCH! I'd looked at that earlier but
the sql book I have only shows literals as being valid 'convert-to'
values. Shoulda just tried it. Thanks again!!!!!sqlsql