How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?

Jul 12, 2004

Hello fellow .net developers,





In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.





Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.





So, what I need to know is: is there a sql statement that can return this type of information?





Example:





Table Names in Database: Customers, Suppliers





Columns in Customers Table: Name, Phone, Email, Address





I click on the word "Customers" in the first dropdownlist.





I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.





I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.





Thanks in advance,





Robert

View 5 Replies


ADVERTISEMENT

Does The Actual Update Statement Of A Table Affect Merge Replication?

Dec 8, 2006

Suppose your using Merge Replication.

2 Users in 2 locations issue updates to the same table. 1 updating 1 column and the other updating another column. Now in reality the actual Stored Procedure issuing the update statement is passed in all the possible columns that could change and builds an update statement that updates all columns even the ones that havent changed.

Will this break Merge Replications conflict tracking? Or does SQL Server 2005 Merge Replication pickup that in reality the 2 updates only in reality changed the values in 2 columns.

View 1 Replies View Related

SELECT Statement To Return Table Column Names

Aug 16, 2004

Is there a SELECT statement that will return a the column names of a given table?

View 5 Replies View Related

Select Into Statement Without Taking Auto Increment Of ID Column To The New Table

Mar 7, 2011

Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.

The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries

Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
 
btw I have used this statement

SET
IDENTITY_INSERT MyTable

ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...

View 4 Replies View Related

Random Selection From Table Variable In Subquery As A Column In Select Statement

Nov 7, 2007

Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?




SET NOCOUNT ON


Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000

Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)

Set @Counter = 0

while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end


Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'

Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'

Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'


select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type

from @NumericBase



SET NOCOUNT OFF

View 14 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Using Column Number Inplace Of Column Name In SQL Select Statement

Jul 20, 2005

Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep

View 1 Replies View Related

Column With Select Statement

Sep 24, 2006

Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this. 

View 1 Replies View Related

SELECT Rowguidcol Vs. SELECT &<actual Name&>

Apr 11, 2008

I understand that I can do this is SQL Server:

CREATE TABLE dbo.test1 (
Col1 int IDENTITY,
ColGUID uniqueidentifier rowguidcol NOT NULL CONSTRAINT DF_test1_ColGUID DEFAULT NEWSEQUENTIALID()
)

INSERT INTO dbo.test1 DEFAULT VALUES

SELECT rowguidcol FROM dbo.test1
SELECT ColGUID FROM dbo.test1


I get this result:

ColGUID
------------------------------------
ED2D1EF0-0408-DD11-A8EB-001AA0D389DB

ColGUID
------------------------------------
ED2D1EF0-0408-DD11-A8EB-001AA0D389DB


Here's the question: Is there any performance difference between using the actual column name and "rowguidcol"?

I would like to be able to script a trigger that uses the rowguidcol column. If I could use rowguidcol instead of the actual column name, I would not have to hard-code the column name in the trigger.

Thanks,
Rob

View 9 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

Return SP In A SELECT Statement Column

Jan 13, 2005

I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement.


I want the following output:


CatID | CatTitle | CatTree
001 | News | exec sp_DisplayTree(@CatID)


My code I tried doesn't work:


SELECT
C.CatID As CatID,
C.CatTitle As CatTitle,
CatTree = (exec sp_DisplayTree C.CatID)
FROM
Cats As C WITH (nolock)



I cannot find a solution to my solution, please help...

View 3 Replies View Related

Create New Column In Select Statement

May 28, 2014

I am trying to create a new column 'COL_4' and, in the same step, create a case statement off of the new col. I know that the code below will not execute. I realize that I could get ride of COL_4 in my code below and concatonate but I need to keep Col_4 in the output.

SELECT
COL_1
,COL_2
,COL_3
,COL_4 = COL_1 + COL_2
,COL_5 = CASE
WHEN COL_1
THEN 'SOMETHING'
END
FROM TABLE_1
;

View 1 Replies View Related

Can You Use Column Order Value In Select Statement

Nov 6, 2005

Is there a shortcut to spelling out column names when you are doing a select statement?

For instance could you write Select 1, 5, 6 from table where whatever...

I tried this but didn't get any results so if you can I must be using wrong syntax.

Thanks
!

View 2 Replies View Related

Possible To Parse A Column In A Select Statement?

Jul 20, 2005

I have a column called SEGMENTED_BLOCK sample data:X,X,XXX,XX,XX,TYZC123456,X,X,TOYZ654321,1234,777777I need to do something that has the effect ofSELECT(stuff before first comma) as FIRST_ITEM,(stuff after first comma, but before second) as NEXT_ITEM,(stuff after second comma but before third(if any)) as THIRD_ITEMFROM SEGMENT_XREFWHERE LOOKUP_ITEM = 12345ORDER BY FIRST_ITEMFIRST_ITEM is pretty easy, but it gets uglier fast.My attempts are horrendously ugly nested checkindex and substring statements.Is there an easier way?

View 2 Replies View Related

How Do I Use A Variable To Specify The Column Name In A Select Statement?

Nov 10, 2006

How do I use a variable to specify the column name in a select statement?

declare @columnName <type>

set @columnName='ID'

select @columnName from Table1

View 8 Replies View Related

How To Display Not A Actual Column

Jan 29, 2015

Query for output this result on Table C ?

========================================================
I have one table store below information, and other one table is store staff name and phone number. how to display of table c data by new column and using the key staff_code1=staff_id or staff_code2= staff_id or staff_code3=staff_id ?
---------------------------------------------------------

Table A
staff_code1,staff_code2,staff_code3

Table B
staff name
staff_id
staff_phone
-----------------------------------------------

Table C display in new column .
staff name-1 code1 staff name-2 code2 , staff name-3 code3
peter id-01 susuan id-03 david id-05

View 1 Replies View Related

How Can I Get Actual Operation Cost From Actual Execution Plan?

Jul 25, 2006

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

View 2 Replies View Related

Adding Column Values Together In SQL SELECT Statement

Aug 22, 2007

I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin.  My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result.  My DB is SQL 2005 Express SP2.  My select statement is below:  SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU,
dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard,
dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM,
dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD,
dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive,
dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video,
dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost,
dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost,
dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost,
dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost,
dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost,
dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost,
dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride,
(dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost
+ dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost
+ dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost)
* ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice
FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID  

View 2 Replies View Related

Dynamic Column Names In Select Statement

Oct 13, 2001

I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.

declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.

The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.

View 1 Replies View Related

Concatenating Column Values In SELECT Statement

Aug 31, 2000

I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression:

SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent
FROM Agents;

I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing.

Any help greatly appreciated. Please cc me privately so I receive your assistance at once!

TIA,
Arthur

View 1 Replies View Related

Update Multiple Column Set Select Statement

Nov 14, 2013

i have a table named masterlist wherein the columns are :

name-----age------sex
andrew---19-------male
trisha---23------female

and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..

View 3 Replies View Related

Select Statement Computed Column Error

Feb 10, 2015

I am receiving error msg on the below query line as "incorrect syntax near MSF" , "Incoorect syntanx near ExtCost", "incorrect syntax near From on line 28"

SELECT
xxxcolumns,
(rj.RECEIVEDLINEAL * ((r.WIDTH / 12.0)) / 1000.0 MSF,
Case
when rv.PricePerCode = 'MSF' Then
((rj.RECEIVEDLINEAL * (r.WIDTH / 12.0)) / 1000.0) * rv.price

[Code] ....

View 1 Replies View Related

SELECT Statement - How To Not Get Column Field Names?

Jan 24, 2007

I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>

View 1 Replies View Related

Transact SQL :: Custom Column In Select Statement

Aug 12, 2015

I want to add a custom column in a select statement that has a value to true or false based on other criteria.

SELECT [ID], [Name], [Description], [EmpID], [Employed] FROM [Employees]

Now, in the above example there is no [Employed] Column in my table but I want it to show true or false based on whether or not [EmpID] equals a certain value.

View 6 Replies View Related

Hiding Or Removing Column Output From Select Statement

Feb 22, 2005

I'm executing the following...

select COL1, min(COL2) from TABLE group by COL1

the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

tia

a

View 2 Replies View Related

T-SQL (SS2K8) :: Add Auto Incrementing Column To SELECT Statement

Apr 1, 2013

Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:

YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.

I have the YYMMDDGP part down with the following expression:

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]

Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck.

View 7 Replies View Related

T-SQL (SS2K8) :: Pass A Column Name Using Parameter In Select Statement

Sep 5, 2014

I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.

Here's the table structure, let's call it #temp

[Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]
Bike, 200
Accessory , 100

I have a table in the DB created as [ProductSales]

Here's what works for me:

insert into [ProductSales](Product, Sales)
Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp

But in reality the product level is not always on level 03, I want to define a parameter @Product and pass it to the select list.

Here's what I tried to do:

Declare @Product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")
Declare @SQL varchar(MAX)
SET @SQL='insert into [ProductSales](Product, Sales)
Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp'
Exec @SQL

But it threw me error, what is the right way to do it, is it because the double quote or single quote thing?

View 1 Replies View Related

Select Statement - Column To Display Full Name Is 99 Characters

Apr 14, 2015

Select statement. In my database i am using the employee table. I need my first column to display your full name is 99 characters. so like if the employee is john smith it would display Your Full Name Is 9 characters (including the space).

View 2 Replies View Related

Select Statement That Returns The Column Names And Keys

Jul 20, 2005

Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

SQL Server 2014 :: Adding One More Column For SubTotal In Select Statement?

Feb 13, 2014

We have a table with 2 columns 'OrderNo' and 'Amount' as below

ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000

as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below

ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500

View 7 Replies View Related

Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?

Apr 6, 2007

I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.









Table 1

Org

aaa:aaa:aa

aaa:aaa:ab

aaa:aab:aa








Table 2














delim

Seg1Start

Seg1Len

Seg2Start

Seg2Len

Seg3Start

Seg3Len


:

1

3

5

3

9

2



My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.



Any ideas? Can this be done in SSIS?



I'd really appreciate any insight that anyone might have.



Regards,

Bill

View 23 Replies View Related

Select Statement That Will Output Related Rows With Different Column Data Per Row?

Apr 27, 2008

Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:


rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...

where for each ID, I have 3 rows that are associated with it and with different corresponding values.

I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.

i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..

After my loop, I will just do a select * from #someTempTable

Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005

View 2 Replies View Related

Add Column With Fixed Number Of Values (text) To The Select Statement

Aug 1, 2007

Hello,

I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

View 6 Replies View Related







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