Can I Merge a table with itself ?

Hi,
I have a table which contains several integer columns that represent
similiar date related information. ie
RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
I need a view of the table where these different columns have been merged
into a single column, so where there was one row with x number of integers,
I would have X number of rows with a single integer. Is this possible ?
What I am actually wanting to do is sort the rows based on the value that is
highest / lowest from the values in the row.
Can this be done ?
Thanks,
Jon.Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:
> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>|||Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote:
> > Hi,
> >
> > I have a table which contains several integer columns that represent
> > similiar date related information. ie
> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> >
> > I need a view of the table where these different columns have been merged
> > into a single column, so where there was one row with x number of integers,
> > I would have X number of rows with a single integer. Is this possible ?
> >
> > What I am actually wanting to do is sort the rows based on the value that is
> > highest / lowest from the values in the row.
> >
> > Can this be done ?
> >
> > Thanks,
> >
> > Jon.
> >
> >
> >|||Alejandro,
Thanks for these responses.
I like the unpivot one particuarly. I am just seeing if I can get it to work
now !!
I will let you know.
Thanks again,
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
> Bishman,
> If you are working with 2005, then you can also use new operator
> "unpivot".
> create table dbo.t1 (
> a int not null identity unique,
> b int,
> c int,
> d int
> )
> go
> insert into dbo.t1(b, c, d) values(10, 20, 30)
> insert into dbo.t1(b, c, d) values(40, 50, 60)
> go
> select
> a, val
> from
> (
> select a, b as c1, c as c2, d as c3
> from dbo.t1
> ) as pvt
> unpivot
> (val for c in (c1, c2, c3)) as unpvt
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >|||This is working well except I am trying to go past 4 columns ( I need 6)
I get this error when I go to 5 columns....
The type of column "cole" conflicts with the type of other columns specified
in the UNPIVOT list.
with this SQL
USE CastleProspect_Dev
select
ExtractNumber,rowID, RenewalDate
from
(select ExtractNumber,rowID,HomeRenewalMonthDay as a,ContentsRenewalMonthDay
as b,MotorRenewalMonthDay as c, BreakdownRenewalMonthDay as d ,
PetRenewalMonthDay as e from dbo.ExtractDetail) as pvt
unpivot
(RenewalDate for aa in (a, b, c, d, e)) as unpvt
where extractnumber = 99
go
Any Ideas '
Thanks again. This looks like just what I need.
Jon
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
> Alejandro,
> Thanks for these responses.
> I like the unpivot one particuarly. I am just seeing if I can get it to
> work now !!
> I will let you know.
> Thanks again,
> Jon
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>|||Go It !
The two remaining columns have different data type to the other 4, Sorted.
This works really well. Thanks again.
Jon.
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:%23vWauL0iHHA.1388@.TK2MSFTNGP05.phx.gbl...
> This is working well except I am trying to go past 4 columns ( I need 6)
> I get this error when I go to 5 columns....
> The type of column "cole" conflicts with the type of other columns
> specified in the UNPIVOT list.
> with this SQL
> USE CastleProspect_Dev
> select
> ExtractNumber,rowID, RenewalDate
> from
> (select ExtractNumber,rowID,HomeRenewalMonthDay as
> a,ContentsRenewalMonthDay as b,MotorRenewalMonthDay as c,
> BreakdownRenewalMonthDay as d , PetRenewalMonthDay as e from
> dbo.ExtractDetail) as pvt
> unpivot
> (RenewalDate for aa in (a, b, c, d, e)) as unpvt
> where extractnumber = 99
> go
>
> Any Ideas '
> Thanks again. This looks like just what I need.
> Jon
> "Bishman" <jonathan.bishop@.btinternet.com> wrote in message
> news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
>> Alejandro,
>> Thanks for these responses.
>> I like the unpivot one particuarly. I am just seeing if I can get it to
>> work now !!
>> I will let you know.
>> Thanks again,
>> Jon
>>
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>>
>