I have a table called T1 with ten columns Col1, Col2...Col10

Now I want to transform this table to a view called V1 with just two fields
ColName, ColValue where
ColName will be Col1 or Col2... and ColValue will be the actual value in T1.

Can any one make a SQL statement doing this?

I try to use the system tables without hard coded but no luck.

Thanks

Report this post to a moderator

Jac,

You should be able to get what you want this way:

create view V1 as
select
'Col'+right(N,2) as ColName,
case N when 1 then Col1
when 2 then Col2
when 3 then Col3
when 4 then Col4
when 5 then Col5
when 6 then Col6
when 7 then Col7
when 8 then Col8
when 9 then Col9
when 10 then Col10
end as ColValue
from T1 cross join (
select 1 as N union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7
union all select 8 union all select 9 union all select 10
) X

Jac wrote:

> I have a table called T1 with ten columns Col1, Col2...Col10
>
> Now I want to transform this table to a view called V1 with just two fields
> ColName, ColValue where
> ColName will be Col1 or Col2... and ColValue will be the actual value in T1.
>
> Can any one make a SQL statement doing this?
>
> I try to use the system tables without hard coded but no luck.
>
> Thanks

Report this post to a moderator

Thanks.

I tried it and it works except the there are some date and int datatype and
error generated as

"Syntax error converting the varchar value 'PLA123' to a column of data type
int."


"Steve Kass" <skass@drew.edu> wrote in message
news:3D1A831B.23647444@drew.edu...
> Jac,
>
> You should be able to get what you want this way:
>
> create view V1 as
> select
> 'Col'+right(N,2) as ColName,
> case N when 1 then Col1
> when 2 then Col2
> when 3 then Col3
> when 4 then Col4
> when 5 then Col5
> when 6 then Col6
> when 7 then Col7
> when 8 then Col8
> when 9 then Col9
> when 10 then Col10
> end as ColValue
> from T1 cross join (
> select 1 as N union all select 2 union all select 3 union all select 4
> union all select 5 union all select 6 union all select 7
> union all select 8 union all select 9 union all select 10
> ) X
>
> Jac wrote:
>
> > I have a table called T1 with ten columns Col1, Col2...Col10
> >
> > Now I want to transform this table to a view called V1 with just two
fields
> > ColName, ColValue where
> > ColName will be Col1 or Col2... and ColValue will be the actual value in
T1.
> >
> > Can any one make a SQL statement doing this?
> >
> > I try to use the system tables without hard coded but no luck.
> >
> > Thanks
>

Report this post to a moderator

Jac,

I don't understand. There is nothing in the create view to generate that
error. What query caused the error?

Steve

Jac wrote:

> Thanks.
>
> I tried it and it works except the there are some date and int datatype and
> error generated as
>
> "Syntax error converting the varchar value 'PLA123' to a column of data type
> int."
>
> "Steve Kass" <skass@drew.edu> wrote in message
> news:3D1A831B.23647444@drew.edu...
> > Jac,
> >
> > You should be able to get what you want this way:
> >
> > create view V1 as
> > select
> > 'Col'+right(N,2) as ColName,
> > case N when 1 then Col1
> > when 2 then Col2
> > when 3 then Col3
> > when 4 then Col4
> > when 5 then Col5
> > when 6 then Col6
> > when 7 then Col7
> > when 8 then Col8
> > when 9 then Col9
> > when 10 then Col10
> > end as ColValue
> > from T1 cross join (
> > select 1 as N union all select 2 union all select 3 union all select 4
> > union all select 5 union all select 6 union all select 7
> > union all select 8 union all select 9 union all select 10
> > ) X
> >
> > Jac wrote:
> >
> > > I have a table called T1 with ten columns Col1, Col2...Col10
> > >
> > > Now I want to transform this table to a view called V1 with just two
> fields
> > > ColName, ColValue where
> > > ColName will be Col1 or Col2... and ColValue will be the actual value in
> T1.
> > >
> > > Can any one make a SQL statement doing this?
> > >
> > > I try to use the system tables without hard coded but no luck.
> > >
> > > Thanks
> >

Report this post to a moderator

I got it fixed.
I used Col1 to Col10 as column names to simplify my question, actually all
columns have their own name and different datatype.
T1.PLA123 is a int datatype so I have to make it become string; otherwise,
error raised.

SELECT CASE N
WHEN 1 THEN 'ID' WHEN 2 THEN 'DOCID' WHEN
3 THEN 'NAME' WHEN 4 THEN 'STATUS' WHEN 5 THEN 'PLA123'
WHEN 6 THEN 'LABEL' WHEN 7 THEN 'PENDINGDATE' WHEN 8
THEN 'PENDINGUSER' WHEN 9 THEN 'APPROVEDDATE' WHEN
10 THEN 'APPROVEUSER' END AS AttributeName,
CASE N
WHEN 1 THEN T1.ID WHEN 2 THEN T1.DOCUMENTID
WHEN 3 THEN T1.NAME WHEN 4 THEN CONVERT(CHAR,
T1.STATUS) WHEN 5 THEN CONVERT(CHAR,
T1.PLA123)
WHEN 6 THEN T1.LABEL WHEN 7 THEN CONVERT(CHAR,
T1.PENDINGDATE, 101)
WHEN 8 THEN T1.PENDINGUSER WHEN 9 THEN CONVERT(CHAR,
T1.APPROVEDDATE)
WHEN 10 THEN T1.APPROVEUSER END AS AttributeValue
FROM T1 CROSS JOIN
(SELECT 1 AS N
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
) X


"Steve Kass" <skass@drew.edu> wrote in message
news:3D1B6286.BDE2C67@drew.edu...
> Jac,
>
> I don't understand. There is nothing in the create view to generate
that
> error. What query caused the error?
>
> Steve
>
> Jac wrote:
>
> > Thanks.
> >
> > I tried it and it works except the there are some date and int datatype
and
> > error generated as
> >
> > "Syntax error converting the varchar value 'PLA123' to a column of data
type
> > int."
> >
> > "Steve Kass" <skass@drew.edu> wrote in message
> > news:3D1A831B.23647444@drew.edu...
> > > Jac,
> > >
> > > You should be able to get what you want this way:
> > >
> > > create view V1 as
> > > select
> > > 'Col'+right(N,2) as ColName,
> > > case N when 1 then Col1
> > > when 2 then Col2
> > > when 3 then Col3
> > > when 4 then Col4
> > > when 5 then Col5
> > > when 6 then Col6
> > > when 7 then Col7
> > > when 8 then Col8
> > > when 9 then Col9
> > > when 10 then Col10
> > > end as ColValue
> > > from T1 cross join (
> > > select 1 as N union all select 2 union all select 3 union all select
4[col
or=darkred]
> > > union all select 5 union all select 6 union all select 7
> > > union all select 8 union all select 9 union all select 10
> > > ) X
> > >
> > > Jac wrote:
> > >
> > > > I have a table called T1 with ten columns Col1, Col2...Col10
> > > >
> > > > Now I want to transform this table to a view called V1 with just two
> > fields
> > > > ColName, ColValue where
> > > > ColName will be Col1 or Col2... and ColValue will be the actual[/color]
value in
> > T1.
> > > >
> > > > Can any one make a SQL statement doing this?
> > > >
> > > > I try to use the system tables without hard coded but no luck.
> > > >
> > > > Thanks
> > >
>

Report this post to a moderator

I mean creating the view has no error, but running the view does.



"Steve Kass" <skass@drew.edu> wrote in message
news:3D1B6286.BDE2C67@drew.edu...
> Jac,
>
> I don't understand. There is nothing in the create view to generate
that
> error. What query caused the error?
>
> Steve
>
> Jac wrote:
>
> > Thanks.
> >
> > I tried it and it works except the there are some date and int datatype
and
> > error generated as
> >
> > "Syntax error converting the varchar value 'PLA123' to a column of data
type
> > int."
> >
> > "Steve Kass" <skass@drew.edu> wrote in message
> > news:3D1A831B.23647444@drew.edu...
> > > Jac,
> > >
> > > You should be able to get what you want this way:
> > >
> > > create view V1 as
> > > select
> > > 'Col'+right(N,2) as ColName,
> > > case N when 1 then Col1
> > > when 2 then Col2
> > > when 3 then Col3
> > > when 4 then Col4
> > > when 5 then Col5
> > > when 6 then Col6
> > > when 7 then Col7
> > > when 8 then Col8
> > > when 9 then Col9
> > > when 10 then Col10
> > > end as ColValue
> > > from T1 cross join (
> > > select 1 as N union all select 2 union all select 3 union all select
4[col
or=darkred]
> > > union all select 5 union all select 6 union all select 7
> > > union all select 8 union all select 9 union all select 10
> > > ) X
> > >
> > > Jac wrote:
> > >
> > > > I have a table called T1 with ten columns Col1, Col2...Col10
> > > >
> > > > Now I want to transform this table to a view called V1 with just two
> > fields
> > > > ColName, ColValue where
> > > > ColName will be Col1 or Col2... and ColValue will be the actual[/color]
value in
> > T1.
> > > >
> > > > Can any one make a SQL statement doing this?
> > > >
> > > > I try to use the system tables without hard coded but no luck.
> > > >
> > > > Thanks
> > >
>

Report this post to a moderator