473,549 Members | 2,383 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating view (transpose records to columns)

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)
Jul 20 '05 #1
2 4796
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
Jul 20 '05 #2
Ross Presser <rp******@imtek .com> wrote in message news:<5c******* *******@rpresse r.invalid>...
Thanks a million Ross
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Jonathan | last post by:
In the organisation where I work we currently have a public sanitised version of our existing HR personnel database. This is very widely used as the main source of employee info by many internal bespoke applications. For most of the tables, the structure of the published out table is exactly the same as in the master personnel database. ...
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about 25-50). This allows each user to have their own set of selections. The selections table has three fields: ID (int), Sel (bit), MachName (varchar). ID...
by: jenny.rhodes | last post by:
Hello, Please can anyone guide me on how to transpose an access table where I have many records per id eg UserID Question Answer 1 1 a 1 2 d 1 3 c 1 4 d
by: Filippo Pandiani | last post by:
I have a grid that shows the file list from a folder. On the postback, how do I get a Dataset from this grid? Thanks, Filippo.
by: Chris Smith | last post by:
Experience Posters, Sorry if this is not the right group to post this question. He is my issue; Is there a way without the use of 3rd party controls, to transpose the rows of a datatable to columns of the datatable? Any help would be great.
by: Leszek Gruszka | last post by:
I wrote an aplication that write something into tableA in sql2000. I want to write the same, but transposed into tableB. Someone can help me? Any example? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!
by: Leszek Gruszka | last post by:
I wrote code, that fill TableA with records by executenonquery. But i want to have second TableB, that will be transposed TableA. My code: Public Sub SQL_Wpis() Dim sSQL As String sSQL = "SELECT * FROM KanaSecRep" Dim objConn As New SqlConnection(sConnection) Dim objDataAdapter As New SqlDataAdapter(sSQL, objConn) Dim objDS As New...
by: hummer | last post by:
In an Access query, I am trying to do the following: I have this: Account # Issue Problem ------------------------------------------------------------------- 1 a Y 2 a N 2 h Y 2 w ...
by: jenniferhelen | last post by:
I have been searching threads for a while and found the instructions listed below many times, however when I get to step 6 and select to save, I always receive the following error, "The information cannot be pasted because the copy area and the paste area are not the same size and shape". Does this function only work if the number or rows and...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.