Creating View (transpose Records To Columns)

Jul 20, 2005

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)

View 2 Replies


ADVERTISEMENT

Creating Columns In A View

May 10, 2006

Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help

Create View BONUS_PAYOUTS As

Alter Table Employee
Add EMP_BONUS Decimal(6,2)

Update Employee
Set EMP_BONUS = EMP_PCT / 500000

Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS
From Employee
Inner Join Position On Employee.POS_ID = Position.POS_ID

View 5 Replies View Related

Creating Records Based SQL View

Oct 30, 2007

I have a spreadsheet that we download from one of our customers that contains Sales Order Release information. The spreadsheets contains 1-n releases per part #.

I have a SQL 2000 view that groups this information into a smaller table (after importing into SQL).

I need to take this view and create 1-n release records based on our production run qty and put it in a new table. I have a field in my view that contains the desired # of records to create.

Since I never used TSQL to loop through table/view, how do I do this. Following is what the output should look like:

From SQL view
---------------------------------------
Part #: 11124A1
Qty Due:175
Run Qty: 50
# Release to Generate: 4


Output to Table
----------------------

Part # QtyDue RunQty
----------------------------------------------------
11124A1 50 50
11124A1 50 50
11124A1 50 50
11124A1 25 50

Note QtyDue is the production run qty, with the last record containing the remaining QtyDue from view's Qty Due

View 4 Replies View Related

Creating A View With Columns Not In Another Table

Jul 20, 2005

Hello,I'm somewhat of a newbie and I need to create a view with a columnthat is not derived from any other tables. What I want is for thisfield to be an auto-increment field or some kind of UID. Can anyonequickly tell me how to do this.Here is my dilemma. I need to pull data from a unique data set from atable which does not have a primary key, and none exists in its data.Please tell me how to put this data in a view(or another table) sothat I can pull it out with unique records. It doesnt matter what theunique field consists of.Thanks

View 3 Replies View Related

Transpose Rows Into Columns

Oct 22, 2007

Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table

ID type
1 A
1 B
2 A
2 C
2 D
etc,....




I want to tranpose

to some like

ID type
1 A,B
2 A,C,D
etc,...


or

ID type1 type2 type3
1 A B
2 A C D

etc,....


Really appreciate any input.

View 3 Replies View Related

Transpose Rows To Columns

Apr 23, 2008

I have the below table:
Pallet1 ItemA1
Pallet1 ItemB1
Pallet1 ItemC1
Pallet1 ItemD1
Pallet1 ItemE1
Pallet2 ItemA2
Pallet2 ItemB2
Pallet2 ItemC2
Pallet2 ItemD2


Would like to Transpose it to
Pallet 1 ItemA1 ItemB1 ItemC1 ItemD1 ItemE1
Pallet 2 ItemA2 ItemB2 ItemC2 ItemD2 NULL

Would be thankful for Any help.

View 2 Replies View Related

Transpose Rows To Columns ?

Mar 30, 2006

I have recordsID Sku Name Date2 41 Blair 01/04/033 45 John 03/04/03that should look like...ID 2 3Sku 41 45Name Blair John.....and so on. Number of source rows will be fixed (12) so no of targetcolumns will be 12 too.Anyone know of a quick way to do this via simple SQL ?

View 3 Replies View Related

Transpose Dynamic Rows To Columns

Mar 23, 2015

how to transpose rows to columns . there are 800 dynamic rows

device_idUserNameipaddrmacaddrLastConnecttimeOperatingSystemRAMCPUTOTALSIZEFREESPACEpercentageDriveNameName
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 ActiveX [15.0.0.239]
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 Plugin [15.0.0.239]

[code]...

View 9 Replies View Related

SQL Server 2008 :: Transpose Columns To Rows?

Oct 29, 2015

I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.

I have data in the form of:

Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,

The output should look like:

Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black

View 2 Replies View Related

Transact SQL :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payer varchar (5),                  
ID varchar (5),      
ClM varchar (2),                 
Paid1 float,
Paid2 float,
Paid3 float
)                      

[code]....

Desired result:

Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
 001 002
76.58 19.53
 153.48 96.43
 53.48   200

View 10 Replies View Related

Attempting To Transpose/pivot Columns To Rows... I Think.

Jun 26, 2006

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

Date______OrderNum____WorkDay

2006-06-1__AA_________1

2006-06-1__AB_________1

2006-06-2__BA_________2

2006-06-2__BB_________2

2006-06-2__BC_________2

2006-06-5__CA_________3

2006-06-5__CB_________3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1______Day2______Day3_______etc.

AA________BA_______CA_________etc.

AB________BB_______CB_________etc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

 

View 7 Replies View Related

SQL Server 2012 :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,

[Code] ....

Desired result:

Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200

View 6 Replies View Related

SQL Server 2012 :: Transpose Values From Rows To Columns Based On Name

Nov 12, 2014

I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.

View 6 Replies View Related

Transact SQL :: Transpose Part Of Rows To Columns In Single Select Statement

Aug 31, 2015

Below. I have also pasted the current result of this query and the desired result. 

Query can be updated to get the desired result as given below?

Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'

Current Result which I am getting with this query

OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01

Desired/Expected Result for which I need updated query

OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3

5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01

Using DB2.

View 2 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

Matching A View's Columns To It's Underlying Table's Columns

Jul 20, 2005

Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name

View 2 Replies View Related

Creating A View Help

Aug 6, 2007

i have a table which has 2 columns 1 'report'  2 'part'
now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50'
i want to create a view that will put them together in format like this:
1111110001
1111110002
1111110003 .. and on
it needs to be in 10 digits.
is there anyway i can create a View or may be a column in the table which can create the #'s in this format.

View 2 Replies View Related

Creating A View

Dec 5, 2005

What are some possible purposes of creating a view and how can it be used to reinforce data security. What description of circumstances can be used for a view to save re-programming?

View 1 Replies View Related

Help With Creating View

Apr 16, 2008

Hi All,

I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 2 Replies View Related

Help With Creating View

Apr 16, 2008

Hi All,

I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 2 Replies View Related

Need Help With Creating View

Jul 23, 2005

I need to create a view that scores a research assessment. So I havequestions 1 through 10, but the problem is that Q3 and Q5-10 have to berecoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.So this is what I have. I keep getting an error message that there is"incorrect syntax near the keyword THEN". I don't know which "THEN" itis referring to (or all of them)?? HELP! I am new to this.CREATE VIEW name ASSELECT ID, DATE, TOTAL=Q1+Q2+CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN0)+Q4+CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN0)+CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN0)+CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN0)+CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN0)+CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN0)+CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3THEN 0) ENDFROM tablename

View 2 Replies View Related

Help With Creating View

Apr 16, 2008



Hi All,


I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with €˜#€™ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 3 Replies View Related

Question About Creating A View.

May 19, 2007

I currently have a website with a page that displays the flags/ keys of the entire roster of guilded characters.  Some more background is that I run a website for my Everquest guild, users can log in , create characters , and update their flags / keys.
 There are 4 associated tables that are used in displaying the flags.
 the Characters , Flags, Expansion, and Char_Flags tables.   The char_flags table consists of 2 foreign keys and a bit field for enabled / disabled. 
Char_flags = char_fk, flag_fk, enabled (bit)
The select statement I'm currently using to get information is as follows.
SELECT Expansion.ExpansionName, Flags.Flag_Name, Characters.Char_Name, char_flags.enabled FROM char_flags INNER JOIN Flags ON char_flags.Flag_FK = Flags.Flag_PK INNER JOIN Expansion ON Flags.Expansion_FK = Expansion.ExpansionPK INNER JOIN Characters ON char_flags.Char_FK = Characters.Char_PK WHERE (Expansion.ExpansionPK = @expansion)
 That displays the information in a format like
Expansion Name,  FlagName, CharacterName, Enabled.   And there are usually 10 -15 flags per expansion so this lists the expansion 10 times, each flag name, then the character name 10-15 times, and wether or not the flag is enabled.
 
I would like to display the information a little differently and I'm not sure how to.   I would like to display the inormation like this
             Flag Name 1     Flag Name 2,  Flag name 3, Flag Name 4, etc...
Char 1          X                                            X                  X
Char2                                  X                     X                  X
Char 3         X                      X                      X                 X
Char 4
Char 5
etc
where the characters make up the first column, the flag names make up the first row(headers) and wether or not the flag is enabled is in the column under the corresponding flag.
This way the name of the flag, and the character name are only displayed one time instead of the flags and character names being repeated over and over.  
If anyone can help me on how to display the data I would appreciate it.   Here is a link to the page to show how it looks now if it helps www.shattereddestinies.net/flagstest.aspx
Thanks
Brad Fortner

View 3 Replies View Related

Problems Creating A View

Jan 19, 2005

I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @textbox. Here's what I have so far:
USE [Maindb]
GO
CREATE VIEW [tblMain_view] (@textbox nvarchar(30)) ????
AS SELECT dbo.tblMain.Field1, ....
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @textbox and ....


First of all, I know that where I declare @textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use:
cmd.SelectCommand.Parameters.Add . . .
in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. Thanks

View 1 Replies View Related

Creating View On Another Database

May 23, 2006

Hi I have two SQL server 2000 machine in my corporateHow to create a SQL View if the database table is located on another SQL server?Example :I am currently working at SQL Server "S01", but need to create a sql view to table customer on "Marketing" database located on SQL Server "S02"if the table is reside on the same machine then it's simple for me, but this customer table is not located on "S01" but located on different machine "S02"how to do this? any tutorial?thanks a lotYonscun

View 2 Replies View Related

Creating View Through Sproc

Jun 15, 2001

Hi guys,
I am trying to create a view through SP
as follows, but I am getting as error as Invalid syntax near view.
Please let me know where I am doing wrong.

Thanks,Rau
CREATE PROCEDURE PurgeReport
@intJob as int
AS
if @intjob=1
begin
CREATE VIEW Purge AS SELECT Btch_id AS Batch_ID, DLN, Process,End_DLN, Job,
Tcode
FROM Batch_HDR
end

View 2 Replies View Related

Creating Indexes On View

Nov 9, 2000

Hi

I want to know if we can create indexes on Views in SQL 7.0. If yes, then how

Thanks

alsi

View 1 Replies View Related

Help Needed For Creating View

Sep 2, 2004

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required........

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.


Thanks

View 6 Replies View Related

Creating A View With A New Column

Jan 31, 2014

I'm using SQL Server 2008 to solve the following exercise.

I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone

I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders

View 4 Replies View Related

Creating A View Using A Union?

Jan 14, 2015

I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.

This is my code so far:

Create View Table_AB As
Select *From Table_A
Union All
Select*From Table_B

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved