Creating view (transpose records to columns)

[Sanjay Asrani]

I have a table like the following

Field1 Field2 Field3
------ ------- ------
x1 y1 z1
x1 y2 z2
x1 y3 z3
x1 y4 z4
x2 y1 z5
x2 y2 z6
x2 y3 z7
x2 y4 z8
x3 y1 z9
............and so on
I want to create a view with x1, x2, x3.. as unique
records; y1, y2, y3.... as fields; and z1, z2, z3.... as the values

When I do

CREATE VIEW xyz (y1, y2, y3, y4) AS
SELECT field1 ,
( SELECT field3 FROM table WHERE field2 = 'y1'),
( SELECT field3 .....
FROM table

I get the error that the sql query creates duplicate values. I think I
may have to do a join using distinct values of field1. I was looking
for some guidance with the join.

Thanks for your help in advance

(using SQLSERVER 2000)

[Ross Presser]

On 1 Oct 2004 06:09:37 -0700, Sanjay Asrani wrote:

I want to create a view with x1, x2, x3.. as unique
records; y1, y2, y3.... as fields; and z1, z2, z3.... as the values

Perhaps this is what you want (with DDL statements included):

create table T1 (
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
primary key (col1,col2)
)

insert into T1 (col1, col2, col3) values ('x1','y1','z1')
insert into T1 (col1, col2, col3) values ('x1','y2','z2')
insert into T1 (col1, col2, col3) values ('x1','y3','z3')
insert into T1 (col1, col2, col3) values ('x1','y4','z4')
insert into T1 (col1, col2, col3) values ('x2','y1','z5')
insert into T1 (col1, col2, col3) values ('x2','y2','z6')
insert into T1 (col1, col2, col3) values ('x2','y3','z7')
insert into T1 (col1, col2, col3) values ('x2','y4','z8')
insert into T1 (col1, col2, col3) values ('x3','y1','z9')

select col1,
min(case when col2='y1' then col3 else null end) [y1],
min(case when col2='y2' then col3 else null end) [y2],
min(case when col2='y3' then col3 else null end) [y3],
min(case when col2='y4' then col3 else null end) [y4]
from T1
group by col1

Here's the result:
col1 y1 y2 y3 y4
-----------------------------------
x1 z1 z2 z3 z4
x2 z5 z6 z7 z8
x3 z9 NULL NULL NULL

[Sanjay Asrani]

Ross Presser <rp******@imtek.com> wrote in message news:<5c**************@rpresser.invalid>...
Thanks a million Ross