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