Same Field From Several To Seperate Fields Of A Single Row
Nov 11, 2007
Hi,
I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.
Hi All. I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query. This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:- Column Name Data Type OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both. SELECT OrderNo + ' ' + Cost AS Expr1 FROM [Order] Do i have to convert them both to string/varchar first? If so, how? Thanks for your replies.
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1
The only way around it now is that I store Other_Name in Table1.
I have two tables in a SQL db. Each has 3 separate fields used to store a date info:
lastservicemonth tinyint1 lastserviceday tinyint1 lastserviceyear smallint2 (and these fields can be nulls)
I want to compare the date info in Table A vs. Table B and find the latest date between the two.
I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates. But ths far I have been unsuccessful.
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
Hi,All: I am using Sql 2000 db to storage my data,and I have a table including a column named "ScanDate" (Type: SmallDateTime),Now I just want to save the current system date to this column when I save my system data. when I saved it and I found that column value include the time data,not only date . So How can I just save date to my samlldatetime typed column ? thanks in advanced!
I have a very complex (for me, anyway) data transformation problem.
I've been given a flat-file of physician data from another system which must be automated for entry into the SQL server on a regular basis.
This was no problem until we discovered that several fields (all of which we wanted to use) had multiple pieces of information in them, separated by semicolons.
Well, this didn't seem to be too big of a problem, so I wrote a DTS activeX script to handle it. This is what I originally wrote:
'******************* Function Transform() 'Declare variables Dim strOffice Dim strOfficeNew Dim cChar Dim x Dim y Dim z
'Scrub values into new rows strOffice = DTSSource("Col050") x = 1 y = len(strOffice) z = 1
While x <= y cChar = Mid(strOffice, x 1) If cChar <> ";" Then strOfficeNew = strOfficeNew & cChar Else DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z DTSDestination("Addr_Office") = strOfficeNew strOfficeNew = "" z = z + 1 End If x = x + 1 Wend
'Insert final record after last semicolon If strOffice <> "" Then DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z DTSDestination("Addr_Office") = strOfficeNew End If
Transform = DTSTransformStat_OK End Function '*********************
This, of course, didn't work. WHat I got was the last part of the parsed data, which for the first record, was the second Address in the field.
I searched around, and found the following script that is supposed to allow multiple rows off of a single row, but I can't seem to merge the two and still get the data out clean.
'********** Dim nCounter nCounter = 4
Function Main()
if nCounter > 0 then Main = DTSTransformStat_SkipFetch DTSDestination("PatientNumber") = DTSSource("PatientNumber") Select Case nCounter Case 1 DTSDestination("PhysicianType") = "Admitting" DTSDestination("PhysicianId") = DTSSource ("AdmittingPhysician") Case 2 DTSDestination("PhysicianType") = "Attending" DTSDestination("PhysicianId") = DTSSource ("AttendingPhysician") Case 3 DTSDestination("PhysicianType") = "Referring" DTSDestination("PhysicianId") = DTSSource ("ReferringPhysician") Case 4 DTSDestination("PhysicianType") = "Consulting" DTSDestination("PhysicianId") = DTSSource ("ConsultingPhysician") End Select nCounter = nCounter - 1 else nCounter = 4 Main = DTSTransformStat_SkipInsert end if End Function '**************
I'm not a VB Script expert, so there's probably something very simple that I'm missing here... if someone could point it out, I'd be greatly appreciative.
How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.
Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.
Hi I need to export data to CSV file from Sql Reporting Services. I am including single quotations in my view to display in SRS for string fields. After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
need help on update from one table to another like this this is my first table
tb_all_holiday
id
fname
Start_Date
End_Date
val_holiday
111 aaaa
15/03/2008 21/03/2008
1
222 bbbb
02/05/2008 09/05/2008
3
333 cccc
03/04/2008 15/05/2008
4
333 cccc
29/04/2008 07/07/2008
1
444 dddd
01/05/2008 02/05/2008
1
444 dddd
09/05/2008 19/08/2008
1
555 EEE
09/07/2008 09/08/2008
4
666 fff 10/09/2008 12/09/2008
1 this is my second table to insert into !
i need to insert to another table like this single row for each day from start_date TO END_DATE check each employee add row for each day insert all employee one after one
ID fname new_date val_holiday ----------------------------------------------------
While working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
question need help how can i use this code below not for insert i need it for update another table but only if exist the link to the code in this FORUM http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet declare @t table( id int, fname char(4), Start_Date datetime, End_Date datetime, val_holiday int ) insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 ) insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3) insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4) insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday from @t t1
inner join Calendar cal on cal.isodate >= t1.start_date and cal.ISODate <= t1.end_date
I am developing an application for a big office which uses SQL Server 2000. Apart from my database, on that server, there are two databases by other companies. The administrator also has access to server but the client only wants him to backup the database.
I have two questions:
1) First of all (if it is possible) I would like to protect my own database from the other companies.
I don't want them to:
see the data in the tables (around 20 tables) make changes to the stored procedures (more than 100 stored procedures) be able to backup the database
2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden. I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
SELECT DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email --------- --------- <any> abc@abc.com <any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
(select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID
FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname. I am at a loss and would greatly appreciate any help.
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECT TXPRCL, TXALTR, TXANAM FROM dbo.PCWEBF21 WHERE (TXALTR > 0) UNION SELECT TXPRCL, TXTAXP, TXTNAM FROM dbo.PCWEBF21 WHERE TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.
