Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column
Jan 15, 2008
I have detail table like
date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8
Result View
=======
Item_id A B O T Total
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price from tblCustomerPurchases as a join tblProduct as b on a.ID=b.ID
I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table.TIALaurence Breeze
I have two tables, one is called (questions), the second one (answers).
questions columns are (ID,questionTitle)
answers columns are (ID,questionID,answer, answerDate)
I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.
date item_id grade in out ------ ------- ------- ----- ----- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total ------- --- ---- --- --- ------- 001 10 0 8 -10 8 002 0 0 1 0 1 003 0 -8 20 -10 2
I want result group by Item_id and sum of grade in column . where grade could be any Alphabet, the column of result query could varies. Each grade contains sum of (in-out) of item_id of detail table.
Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id], [Project1].[DirectoryId] AS [DirectoryId], [Project1].[SPI] AS [SPI], [Project1].[FirstName] AS [FirstName], [Project1].[LastName] AS [LastName], [Project1].[NPI] AS [NPI], [Project1].[AddressLine1] AS [AddressLine1], [Project1].[AddressLine2] AS [AddressLine2],
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
I have created a virtual cube in SQL2000. Then, I have generated aCalculated Member. When I did rollup into a higher level, SQL2000 didre-calculate the member. However, I want to summerize the Calculatedmember. The Solve Order could not be worked in this situation since ameasure, which is used to generate the Calculate Member, will beavailable only in the lowerest level of the dimension. Please help me!
I want to convert all the column of table to ToBase64String.
Public Class ScriptMain Inherits UserComponent Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider() Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014) Dim hash As Byte() = md5.ComputeHash(columnContents) Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None) Row.RowChecksum = hashString End Sub End Class
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
I am listing detail transaction lines in a table sorted by account and order number. the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
i use microaccess create table, there is a filed call"Complete_PO", value"yes/no" i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc" PoNum_SqlDataAdapter.Fill(PO_DataSet1) TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable. INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in -- descending order
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like:
Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,.... From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID Where Order.OrderID=10
so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...." How can I write this query in T-SQL? Thanks
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
HiI've two tablesTableAidnameTableBidtableA_iddescoperAs you can see tableA is a master and tableB is a detail table wherewe can have many records for each related tableA record.I need to get all records for tableA with a count on some oper oftableB.I suppose I can got it with a join or a subselect but I don't use SQLoften so I'm getting crazy with this stupid query...Could somebody help ?Thanks in advanceC
