Split Address Into Multiple Field
i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start?
lets say i have "12 north plaza boulevard apt.16" and i want it to become:
address_number
12
suffixA
North
street_name
plaza blvd
suffixb
apt16
View Complete Forum Thread with Replies
Related Forum Messages:
Split One Field In Multiple New Rows
HiHo, just a beginners question: I have the following row with 2 fields: Field 1: Task A Field 2:´ 1;2;3;4 The number of semicolon divided elements in Field 2 is variabel. I would like to create new rows like: Row 1 Field 1: A Field 2: 1 Row 2 Field 1: A Field 2: 2 Row 3 Field 1: A Field 2: 3 Row 4 Field 1: A Field 2: 4 I think I should use a Foreach Loop. But I don't exactly how to do it? best regards Chris
View Replies !
Split Address Value Into Two Columns
I am trying to get an address field into 2 colums. I need the number value in one column and street name in another column. The data is stored: 876 blue ct 9987 red dr 23 windyknoll This is what I haveelect substring(Address,0,charindex('',Address)) as number ,substring(Address, (charindex('',Address)+1) ,len(Address)) as address from contact
View Replies !
Address Field Problem.
I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?
View Replies !
Sorting Address Field By Its Component Parts
The file of about 30,000 records contains an address field where addresses are entered in the following form: 128 NW 8 ST 3444 SW 19 AVE 32 SE 122 TERR 12604 SW 148 PL Then there are anomalies such as 123 E BLVD (I can ignore the anomalies until the main challenge is overcome.) The challenge is that I have been asked to sort the addresses in the following order: quadrant (e.g., NW, SW), street number (e.g., 122), street name (e.g., TERR) house number (e.g., 128) It seemed like I should create a view that can be updated regularly, as records will be added/deleted ongoing. How do I get these part into a four-column view? Or, is there another way to do this? Esther
View Replies !
Split A Field
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI =============================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance =============================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000 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?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI =============================================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance ===================================================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000 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?
View Replies !
Can I Split A Field Based On A Character?
Here's a question for the SQL gurus out there: I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be 8060 8060,4450 8060,4123,3245 Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example CODE1=8060, CODE2=4450, CODE3=NULL. I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field? Any help very greatly appreciated. Thanks, Simon.
View Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
Split Txt File Into Multiple
How can I split this incoming file into separate txts. I want to cut out each Header/detail row section into a new txt. What I mean by header/detail row: incoming txt file: http://www.webfound.net/split.txt basically want to cut out each section like this: http://www.webfound.net/what_to_cut.txt http://www.webfound.net/rows.jpg and a kicker...each new txt name must use a certain field (based on x numbers in header row) followed by another field whcih is the date form the header row. somethign like this: SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 59,4) == "HD" + SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 1,8) == "HD" + .txt I need some hand holding here, it's my first time trying to do something so complicated in SSIS 2005. If I can first just get the txt split into multiple, that would be a big help.
View Replies !
One For The SQL Gurus: Split A Delimited Field Into Rows
Hi. I'm trying to write an SQL Query that will take a delimited field and return each item as a row. Example Take the AuthorizedRoles and TabID fields from the Tabs table AuthorizedRoles TabID 0;11;__________1 0; 15 ;17;______6 -2;____________7 I would like to return a unique record for each Authorized Role AuthorizedRole TabID 0____________1 11___________1 0____________6 15___________6 17___________6 -2___________7 Any ideas? Cheers Dave
View Replies !
Split One Column Into Multiple Columns
Hi all, I have a requirement like this , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno) Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469 How can i acheive this one with out data lose in Address Column. Thanks in advance.
View Replies !
Split Row's Columns Across Multiple Tables
Hello, Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row? As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables. How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row. Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts. Thoughts? Thanks in advance, Dan
View Replies !
Can I Split A Long .sql File Into Multiple Files?
I have one really long .sql file I'm working on. It's actually a data conversion type script. It's gotten really cumbersome to work on as long as it is. I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks
View Replies !
Split Column Data Into Multiple Lines
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below . "FTW*Christopher,Lawson FTW*Bradley,James" Please let me know how can I acheive this?
View Replies !
Split One Row Into Multiple Rows Based On Time Elements
I'm dealing with a problem. The record information example DateTimeStart , DateTimeEnd , action , duration (seconds) 2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600 what i want is for every half hour between start and end a record 10.30 action1 11.00 action1 11.30 action1 how can i create this, i'm a little stuck on this
View Replies !
DTS - Split Single Source Record (text) To Multiple Target (sql)
I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database. As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file. For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure: [date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3] trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows: [date], [custid], [code1], [name1], [amount1] [date], [custid], [code2], [name2], [amount2] [date], [custid], [code3], [name3], [amount3] What is the best way to do this using DTS? I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work Vasilis Siatravanis, siatravanisv@interamerican.gr , vasilliss@hotmail.com
View Replies !
Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports
I am trying to create a report using Reporting Services. My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report. I can currently get one value but how to get the information I need to be able to use in my reports. So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped. Any help would be appreciated. Thanks. I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work. What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields. As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem Thanks for the suggestions that were made, I apoligize for not making the problem clearer. Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting. --Pulls the Service Opportunity SELECT cs.value AS "Service Opportunity" FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid WHERE ca.name = 'Service Opportunity' --Pulls the Number of Hours SELECT cs.value AS 'Number of Hours' FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid WHERE ca.name ='Num of Hours' --Pulls the Person Grade Level SELECT cs.value AS 'Grade' FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid WHERE ca.name ='Grade' --Pulls the Person Number, First and Last Name and Grade Level SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade" FROM student s INNER JOIN cperson cs ON cs.personid = s.personid INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid WHERE cs.value =(SELECT cs.value AS 'Grade' WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View Replies !
Multiple Foreign Keys On Same Field, Based On Other Field
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View Replies !
Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View Replies !
Multiple Ids In A Field
I have a book cataloge where I have an author id linked up to the book.However, I'm finding that some books have mulitple authors, and I'mwondering if there is a way to add more than one id to the author idfield. Or do I have to have many author id fields? Any way around that?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Multiple Options Into One SQL Field
Morning all, I'm writing an Insert form which will write records to a few tables. What I want to know is how do I write multiple answers to one question in different rows in the table but keeping the ID? For example. The form has the following fields: HotelIDHotelFacilities (CheckBoxList) Now each hotel (in this case) will only have one ID but more than one HotelFacility . How do I get my table to read... HotelID HotelFacility 1 Bar 1 Restaurant 1 Cafe 1 Wi-Fi Access I presume INSERT INTO tblHotelFacilities(HotelID, HotelFacility) VALUES(@HotelID, @HotelFacility) won't write more than one selected facility?Thanks,Brett
View Replies !
Put Multiple Results In One Field
Hi all,I need to put multiple results in one field, but not sure how. Here'ssome sample code:select a.name,a.accountnum,a.ordernum,(select itemid from items where items_ordernum = a.ordernum) asItems[color=blue]>From clients a[/color]There's multiple instances of 'itemid' in 'items' with the criteria Igave, so this generates an error. I want to instead have the itemnumbers listed as just a list seperated by commas, so the results mightbe this:Name AccountNum OrderNum ItemsJohn Smith 12345 0000234 1233, 1333, 4322Mike Jones 43223 0000023 4322, 543And so forth. Is there someway to loop through this sub-query and makethe results become a list?Thanks --rlangly
View Replies !
Insert Multiple Values Into One Field
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View Replies !
Updating A Field For Multiple Records
Dear all,I need to update one field in a table for a given record and visit number.Example below is how the table looks -SID VISIT DLCO101 0 12101 1 16102 0 18102 2 10103 1 12103 2 14Here is how I would like it to look. The changes are the starred items.SID VISIT DLCO101 0 14*101 1 16102 0 18102 2 16*103 1 12*103 2 14I know it is an UPDATE statement, but I am not sure how to use it when Ineed to update more than one record.Thanks for the help in advance.Jeff
View Replies !
Join Multiple Records Into One Field
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
View Replies !
Parse Field Into Multiple Rows
Hello,I am loading data from our MS Active Directory into our datawarehouse. (check out Mircosofts's Logparser, it can pull data fromADS, server event logs and more. It can also create text files or loaddirectly to SQL. Its free and a pretty useful tool)There is a field that contains the direct reports of a manager. Thedirect report users are delimited by a pipe symbol.I want to breakup the field into multple rows. There can be none, oneor many direct report users in this field.<disclaimer>This is a snippet of an example. This is only an example. I know thatI have not defined PK nor indexes. My focus is how to solve a problemof parsing a field that has multple values into multple rows.</disclaimer>Thanks for any help in advance.RobCREATE TABLE "dbo"."F_ADS_MANAGERS"("MANAGER_KEY" VARCHAR(255) NULL,"DIRECT_REPORTS_CN" VARCHAR(255) NULL);INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)VALUES ('CN=Marilette, 'CN=RobertD,OU=TechnologyGroup,DC=strayer,DC=edu|CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelleC,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')I want to end up with 5 rows, 1 row for each user that is seprated bythe PIPE symbol.CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Lee K,OU=TechnologyGroup
View Replies !
How To Concatenate Multiple Rows Into One Field?
Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri
View Replies !
Concatenating Multiple Returns On One Field Only
I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?. Example: A query may currently return: Title1 - Subtitle1 - Author 1a - etc Title1 - Subtitle1 - Author 1b - etc Title1 - Subtitle1 - Author 1c - etc Title2 - Subtitle2 - Author 2a - etc Title3 - Subtitle3 - Author 3a - etc When I would like Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc Title2 - Subtitle2 - Author 2a - etc Title3 - Subtitle3 - Author 3a - etc My actual SQL code, if you are interested, is: SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number, dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb, dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors FROM dbo.edition INNER JOIN dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN dbo.party ON dbo.role.party_id = dbo.party.party_id
View Replies !
Concatenate Multiple Records Into One Field
Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance. TABLE ColumnAColumnB 1.......A 2.......B 3.......C 4.......A 5.......A 6.......B 7.......C 8.......D 9.......C 10......E EXPECTED OUTPUT ColumnA ColumnB 1.......4,5 2.......6 3.......7 4.......1,5 5.......1, 4 6.......2 7.......3 8....... 9.......3,7 10......
View Replies !
Concatenate One Field From Multiple Rows?
I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like: Title - Author - Edition The Amazing Pixies - A. N. Author - Hdbk The Amazing Pixies - A. N. Author - Pbk Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.
View Replies !
Same Field On Same Line Multiple Times
I'm new to SQL Reporting Services but have made some decent headway. I'm stumped by one issue though: How do I repeat a field over and over on a single line? To elaborate: I have a table of backup job failures, with a reason and the time it failed. ID | Reason | Time 1 | No tape | 3/13/3008 2 | Bad drive | 3/14/2008 I'm trying to create a summary, and I want to list the information as follows: There were 2 failed backup jobs on 3/13/2008, 3/14/2008 My SQL query is as follows: SELECT Time, COUNT(*) AS NumFailures FROM FailedBackups GROUP BY Time ORDER BY Time ASC How do I "loop" the Time field on a single line in Reporting Services?
View Replies !
How To Implement Unique Key On Multiple Field
hello guys, I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance? thank you.
View Replies !
Multiple Rows Into A Single Field
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_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1, dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id, dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone, dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age, dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type, TBL_TR_ACCIDENT_VEHICLE.Registration_Number, TBL_TR_ACCIDENT_VEHICLE.Crime_No, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre, TBL_TR_ACCIDENT_VEHICLE.vehicle_Make, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model, TBL_TR_ACCIDENT_VEHICLE.Unit_ID, TBL_TR_ACCIDENT_VEHICLE.RowID, TBL_TR_ACCIDENT_VEHICLE.UserID, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color, TBL_TR_ACCIDENT_VEHICLE.HP, TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity, TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle, TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight, TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight, TBL_TR_ACCIDENT_VEHICLE.Skid_Length, (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 end GO
View Replies !
Concatenating Field For Multiple Records
Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example: ID.....Code 5......33 5......23 ID.....Code...Result 5......33.....33 23 5......23.....33 23 I need the code to get the Result field. I know the code if you were to find the sum, min, max, etc... (SELECT SUM(CODE) FROM Table WHERE ID = Table.ID) AS Result But I don't know how to write it so it will combine strings.
View Replies !
Multiple Filtering On The Same Field Using A Stored Procedure
Hello, I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below: Input field: Product Description So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned. SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill" I guess there'll need to be a nested Select and loop to parse the space separated input field. Any pointers would be appreciated. Thank you Lee
View Replies !
|
TRACKER
