Programatically View / Stored Procedure with N fields

We have a table that has 10 fields of which say 5 are either non textual. We
want to have a resultant view with 7 fields, namely 1 numeric field and the
5 non-numeric fields (textual) and one additional field for the non textual
field name. In the example below the last 5 field names are Prod1, Prod2,
Prod3, Prod4, Prod5.
E.g.
Original
SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
Result
SA, Sales, Man1, Actual, Prd1, Jan, 100
SA, Sales, Man1, Actual, Prd2, Jan, 200
SA, Sales, Man1, Actual, Prd3, Jan, 300
SA, Sales, Man1, Actual, Prd4, Jan, 400
SA, Sales, Man1, Actual, Prd5, Jan, 500
SA, Sales, Man1, Actual, Prd1, Jan, 1
SA, Sales, Man2, Actual, Prd2, Jan, 2
SA, Sales, Man3, Actual, Prd3, Jan, 3
SA, Sales, Man4, Actual, Prd4, Jan, 4
SA, Sales, Man5, Actual, Prd5, Jan, 5
In essence the size of the table will end up as
n records * 5 fields.
Thanks in advance.Avril
I'm not sure understood you.
Have you tride using UNION ALL to get what you wanted?
"Avril" <Avril@.ppp.com> wrote in message
news:effVz3oZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> We have a table that has 10 fields of which say 5 are either non textual.
> We want to have a resultant view with 7 fields, namely 1 numeric field and
> the 5 non-numeric fields (textual) and one additional field for the non
> textual field name. In the example below the last 5 field names are Prod1,
> Prod2, Prod3, Prod4, Prod5.
>
> E.g.
> Original
> SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
> SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
> Result
> SA, Sales, Man1, Actual, Prd1, Jan, 100
> SA, Sales, Man1, Actual, Prd2, Jan, 200
> SA, Sales, Man1, Actual, Prd3, Jan, 300
> SA, Sales, Man1, Actual, Prd4, Jan, 400
> SA, Sales, Man1, Actual, Prd5, Jan, 500
> SA, Sales, Man1, Actual, Prd1, Jan, 1
> SA, Sales, Man2, Actual, Prd2, Jan, 2
> SA, Sales, Man3, Actual, Prd3, Jan, 3
> SA, Sales, Man4, Actual, Prd4, Jan, 4
> SA, Sales, Man5, Actual, Prd5, Jan, 5
> In essence the size of the table will end up as
> n records * 5 fields.
> Thanks in advance.
>|||Avril,
In SQL Server 2005, you can use the UNPIVOT operator for this.
In SQL Server 2000, you can use a CROSS JOIN like this:
select
column1, column2, column3, column4,
'Prd' + Nch as Prd,
column5,
case Nch
when 1 then Prod1 when 2 then Prod2 when 3 then Prod3
when 4 then Prod4 when 5 then Prod5 end as LastColumn
from yourTable
cross join (
select '1' as Nch union all select '2' union all select '3'
union all select '4' union all select '5'
) as F
There may be some typos, since you didn't give the create table
statements and sample data as INSERTs to test with, and you
will have to fix column names for the columns whose names
you didn't provide.
Steve Kass
Drew University
Avril wrote:

>We have a table that has 10 fields of which say 5 are either non textual. W
e
>want to have a resultant view with 7 fields, namely 1 numeric field and the
>5 non-numeric fields (textual) and one additional field for the non textual
>field name. In the example below the last 5 field names are Prod1, Prod2,
>Prod3, Prod4, Prod5.
>
>E.g.
>Original
>SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
>SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
>Result
>SA, Sales, Man1, Actual, Prd1, Jan, 100
>SA, Sales, Man1, Actual, Prd2, Jan, 200
>SA, Sales, Man1, Actual, Prd3, Jan, 300
>SA, Sales, Man1, Actual, Prd4, Jan, 400
>SA, Sales, Man1, Actual, Prd5, Jan, 500
>SA, Sales, Man1, Actual, Prd1, Jan, 1
>SA, Sales, Man2, Actual, Prd2, Jan, 2
>SA, Sales, Man3, Actual, Prd3, Jan, 3
>SA, Sales, Man4, Actual, Prd4, Jan, 4
>SA, Sales, Man5, Actual, Prd5, Jan, 5
>In essence the size of the table will end up as
>n records * 5 fields.
>Thanks in advance.
>
>|||Steve
Thanks that worked for me. Now if I wanted to extend that to say
If Column3 = 'Man2' then multiply the amounts by 0.15 or
If Column3 = 'Man3' then multiply the amounts by 0.3
How would this affect the statement below.
Column1 Column2 Column3 Column4 Column5
Prd1 Prd2 Prd3 Prd4 Prd5
SA Sales Man1 Actual
Jan 100 200 300 400 500
SA Sales Man1 Actual
Jan 1 2 3 4 5
SA Sales Man2 Actual
Jan 100 200 300 400 500
SA Sales Man2 Actual
Jan 1 2 3 4 5
SA Sales Man3 Actual
Jan 100 200 300 400 500
SA Sales Man3 Actual
Jan 1 2 3 4 5
select
column1, column2, column3, column4, column5,'Prd' + Nch as Prd,
case Nch
when 1 then Prd1
when 2 then Prd2
when 3 then Prd3
when 4 then Prd4
when 5 then Prd5
end as Amount
from dbo.Sheet1$
cross join (
select '1' as Nch union all select '2' union all select '3'
union all select '4' union all select '5'
) as F
Thanks in advance.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAu7oipZGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Avril,
> In SQL Server 2005, you can use the UNPIVOT operator for this.
> In SQL Server 2000, you can use a CROSS JOIN like this:
> select
> column1, column2, column3, column4,
> 'Prd' + Nch as Prd,
> column5,
> case Nch
> when 1 then Prod1 when 2 then Prod2 when 3 then Prod3
> when 4 then Prod4 when 5 then Prod5 end as LastColumn
> from yourTable
> cross join (
> select '1' as Nch union all select '2' union all select '3'
> union all select '4' union all select '5'
> ) as F
> There may be some typos, since you didn't give the create table
> statements and sample data as INSERTs to test with, and you
> will have to fix column names for the columns whose names
> you didn't provide.
> Steve Kass
> Drew University
> Avril wrote:
>|||On Mon, 24 Apr 2006 06:55:21 +0100, Avril wrote:

>Steve
>Thanks that worked for me. Now if I wanted to extend that to say
>If Column3 = 'Man2' then multiply the amounts by 0.15 or
>If Column3 = 'Man3' then multiply the amounts by 0.3
>How would this affect the statement below.
(snip)
>select
>column1, column2, column3, column4, column5,'Prd' + Nch as Prd,
>case Nch
>when 1 then Prd1
>when 2 then Prd2
>when 3 then Prd3
>when 4 then Prd4
>when 5 then Prd5
>
END * CASE Column3
WHEN 'Man2' THEN 0.15
WHEN 'Man3' THEN 0.3
ELSE 1

>end as Amount
>from dbo.Sheet1$
>cross join (
>select '1' as Nch union all select '2' union all select '3'
>union all select '4' union all select '5'
> ) as F
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Thanks
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:vmgq42tjek1ur7ru4velvtv3hiqnv7gekq@.
4ax.com...
> On Mon, 24 Apr 2006 06:55:21 +0100, Avril wrote:
>
> (snip)
> END * CASE Column3
> WHEN 'Man2' THEN 0.15
> WHEN 'Man3' THEN 0.3
> ELSE 1
>
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP