Combine Multiple Records Into Single Row
This is how the data is organized:
vID Answer
12 Satisfied
12 Marketing
12 Yes
15 Dissatisfied
15 Technology
15 No
32 Strongly Dissatisfied
32 Marketing
32 Yes
What I need to do is pull a recordset which each vID is a single row
and each of the answers is a different field in the row so it looks
something like this
vID Answer1 Answer2 Answer3
12 Saitsfied Marketing Yes
etc...
I can't quite get my mind wrapped around this one.
View Complete Forum Thread with Replies
Related Forum Messages:
Script To Combine Multiple Rows Into 1 Single Row
Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?
View Replies !
Script To Combine Multiple Rows Into A Single Row
Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance
View Replies !
Combine Multiple Rows Into Single SQL Record
Hello: I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on. productID SectionID statusID 10 1 0 10 2 1 10 3 2 10 4 1 10 5 3 10 6 1 11 1 0 11 2 1 11 3 2 11 7 3 11 8 3 Need to return two rows with the respective values for each section. productID section1 section2 section3 section4 section5 section6 section7 section8 10 0 1 2 1 3 1 11 0 1 2 3 3 Any information or if you can point me in the right direction would be appreciated. Thanks
View Replies !
How Can I Combine Values Of Multiple Columns Into A Single Column?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888 And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888 where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
View Replies !
How To Combine Results From Multiple Records Into One
Hello, I have a table which has the following structure: ID MessageText 001 Hello 001 There 001 Working 003 See 003 you 003 Next 003 Time How to build a query or store procedure to return result like this: ID MessageText 001 Hello There Working 003 See you Next Time Your help/advice is greatly appreciated. Thanks, Ficisa
View Replies !
Update Multiple Records From A Single ASP.NET Webpage
Got a beginner question here... Let's say I have a database table that houses server information with four columns: make, model, serial #, ip address. And assume there are ten rows with that information filled out. How could I display all the rows of information on a single webpage (ASP.NET), with all the fields being editable; and a single save button that would send any changes to the database (in reality I guess it would be sending all rows and fields to the database, and just overwrite the previous data). Could a page such as that be created using FrontPage 2003 or Dreamweaver MX 2004? This would be strictly for updating information. I would have a separate form for adding a new entry. Thanks for your help.
View Replies !
Show Multiple Order Records In Single Row
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients. I have data in both table like CompanyTable ID Name 1 name1 2 name2 OrderTable OrderId CompanyTabID 1 1 2 1 3 1 4 1 In my query I want to show all orders in single row. ID Name Orders 1 name1 1,2,3,4 2 name2 null Is anybody can help on it. Thanks Arvind
View Replies !
SP Return Multiple Records For A Single Record
I have two tables TermID, Term 1--- Abc 2--- Test 4--- Tunic and TermID, RelatedTermID 1 --- 2 1--- 4 2--- 4 I need to get back something like this TermID, Term, RelatedTermsInformation 1--- test--- test,tunic#1,4 that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client this does not seem like a very good solution ( or is it?) If posible it would be nice to get something like this TermID, Term, RelatedTermsInformation 1 test RelatedTermsTwoDimentionalArray but I am not sure how this idea could be implemented using the capabilities of SQL. my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found. any ideas in how to make this better ?
View Replies !
Return Single Records By Joining Multiple Tables
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12 [PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0 [AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico [PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy [MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames]. The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.
View Replies !
Update Multiple Varbinary Records With Single Sql Statement
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101; UPDATE HashedPassword = someOtherValue WHERE UserID = 102; ... What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery(). Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx '2. Create a command object for the query Dim strSQL as String = _ "INSERT INTO UserAccount(Username,Password) " & _ "VALUES(@Username, @Password)" Dim objCmd as New SqlCommand(strSQL, objConn) '3. Create parameters Dim paramUsername as SqlParameter paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25) paramUsername.Value = txtUsername.Text objCmd.Parameters.Add(paramUsername) Dim paramPwd as SqlParameter paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16) paramPwd.Value = hashedBytes objCmd.Parameters.Add(paramPwd) 'Insert the records into the database objConn.Open() objCmd.ExecuteNonQuery() objConn.Close()
View Replies !
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date Code: Employee No Date SALARY 1 10/30/2006 500 1 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 4 11/01/2006 8000 Should Appear Code: EmployeeNo Date1 OLDSALARY Date2 NEWSALARY 1 10/30/2006 500 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 11/01/2006 800 PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT. THANKS IN ADVANCE
View Replies !
Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.
I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query: DECLARE @login char(20), /*This sets the rep for the query.*/ @record_date datetime, /*This is the date that we want to run this for.*/ @RWPY decimal(18,2), /*This is the required wins per year.*/ @OCPW decimal(18,2), /*This is the opportunities closed per week.*/ @OACW decimal(18,2), /*This is opportunities advanced to close per week.*/ @TOC decimal(18,2), /*This is the total number of opportunities in close.*/ @OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/ @TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/ @OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/ @TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/ @OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/ @TOI decimal(18,2), /*This is the total number of opportunities in interview.*/ @OCW decimal(18,2), /*This is the opportunities created per week.*/ @TOA decimal(18,2) /*This is the total number of opportunities in approach.*/ SET @login = 'GREP' SET @record_date = '12/18/2007' SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date))) SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SELECT loginname, CAST(@TOA AS decimal(18,1)) AS [Opps in Approach], app_time AS [Approach Average Time], app_perc_adv AS [Approach Perc Adv], CAST(@TOI AS decimal(18,1)) AS [Opps in Interview], int_time AS [Interview Average Time], int_perc_adv AS [Interview Perc Adv], CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate], dem_time AS [Demonstrate Average Time], dem_perc_adv AS [Demonstrate Perc Adv], CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate], neg_time AS [Negotiate Average Time], neg_perc_adv AS [Negotiate Perc Adv], CAST(@TOC AS decimal(18,1)) AS [Opps In Close], cls_time AS [Close Average Time], cls_perc_adv AS [Close Perc Adv] FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record. CREATE TABLE #pipelinehist ( glusftboid int IDENTITY(1,1) NOT NULL, record_date datetime NOT NULL, loginname char(20) NOT NULL, app_new float NOT NULL, app_time float NOT NULL, app_perc_adv float NOT NULL, int_time float NOT NULL, int_perc_adv float NOT NULL, dem_time float NOT NULL, dem_perc_adv float NOT NULL, neg_time float NOT NULL, neg_perc_adv float NOT NULL, cls_time float NOT NULL, cls_perc_adv float NOT NULL, target_deal money NOT NULL, annual_quota money NOT NULL, weeks int NOT NULL ) ON [PRIMARY] INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)
View Replies !
Combine Data In Single Row
SELECT * FROM dbo.empBenefits q WHERE (StartDate IN (SELECT TOP 2 STARTDATE FROM EMPBENEFITS WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON' ORDER BY startdate ASC)) I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
View Replies !
Can We Combine These 3 Statements Into One Single Query
SELECT 1 as id,COUNT(name) as count1 INTO #temp1 FROM emp SELECT 1 as id,COUNT(name) as count2 INTO #temp2 FROM emp WHERE name <>' ' AND name IS NOT NULL OR name <> NULL SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
View Replies !
Combine SQL Records
I have a SQL table with Sales Order release information. Following are some records from the table SMIPartNo QtyType PO POLine QtyDue UOM DateDue DateDueType fsono fcustno finumber frelease fspq JI-117933A1 Firm N40136001 234 200 EA 7/2/2007 0:00 SH Ship Date E00001 20 001 1 1800 JI-117933A1 Firm N40136001 234 400 EA 7/9/2007 0:00 SH Ship Date E00001 20 001 2 1800 JI-117933A1 Firm N40136001 234 400 EA 7/30/2007 0:00 SH Ship Date E00001 20 001 3 180 If sum(QtyDue) is Less than fspq then read next record, if sum(QtyDue)>= fspq then write the record. For the above example the output would look like the following SMIPartNo QtyType PO POLine QtyDue UOM DateDue DateDueType fsono fcustno finumber frelease fspq JI-117933A1 Firm N40136001 234 1000 EA 7/2/2007 0:00 SH Ship Date E00001 20 001 1 1800 Any help would be nice. Thanks David Davis
View Replies !
Any Ideas On How To Combine Records Into One
Here is what I have, select id, name from rss_user gives me this r604738 one r604738 two r604738 three r604739 one r604739 two r604739 three r604739 four I would like to be able to pipe this into a @temp table so it looks like this, r604738 one,two,three r604739 one,two,three,four Any ideas, so far I am drawing a blank.
View Replies !
Combine Records On Two Fields
Hello everyone - This is my first post to the forum and I'm very new to SQL. I apologize if this is addressed elsewhere. Here is an example of the results I am getting from my query AdmissionID, sNurseInit, sSWInit 100, {NULL}, SAE 100, REG , {Null} Is there a way to combine (merge, join? I don't know the right word) these records so that a single record for the admission is returned? AdmissionID, sNurseInit, sSWInit 100, REG, SAE Thanks in advance! Amy
View Replies !
Compile/combine The Contents Of Several Records.
I have the following table;CREATE TABLE [x_Note] ([x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,[Note] [varchar] (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_x_NoteId] PRIMARY KEY CLUSTERED([x_NoteId],) WITH FILLFACTOR = 90 ON [USERDATA] ,) ON [USERDATA]GOMy clients want me to take the contents of the Note column for each rowand combine them. In other words, they basically want:Note = Note [accumulated from previous rows] + Char(13) [because theywant a carriage return] + Note [from current record].What is the most efficient and relatively painless way to do this? Ithink it might require a cursor, but I'm not sure if there is a moreelegant set-based method to make this happen.
View Replies !
How To Combine 2 Records Into 1 Unique Record
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
View Replies !
Combine 4 Tables Without Repetitive Records
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields. The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected. Thanks
View Replies !
Simple Cursor To Combine Records
I need to return one record with concatenated string fields from a table that may contain several records. I think a cursor will be able to do what I want, but I'm not very experienced at writing them. My data HDR DMCD 107 TEX 107 AIR 107 LG 108 TEX 108 CAR 109 SM I want the result of my query to find adn return each header and return the 1 or more DMCD field values concatenated. i.e. 107 TEX AIR LG 108 TEX CAR 109 SM This is my attempt at the cursor so far SET NOCOUNT ON DECLARE @AACODE varchar(50),@hdr varchar(20),@dmcd varchar(20) DECLARE AAROW_cursor CURSOR FOR SELECT aaglhdrid,aatrxdimid FROM aag30003 OPEN AAROW_cursor FETCH NEXT FROM AAROW_cursor INTo @hdr, @dmcd WHILE @@FETCH_STATUS = 0 BEGIN set @aacode=@aacode+@dmcd select @hdr,@dim,@aacode FETCH NEXT FROM aarow_cursor INTO @hdr, @dmcd END CLOSE AAROW_cursor DEALLOCATE aarow_cursor
View Replies !
Help: Need To Combine Multiple IF Queries
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View Replies !
Combine Multiple Sql Calls Into 1
I have an old app that I'm trying to recode and improve performance. From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call. Here is a quick example of the script Select * from table1 where id = " & tempVariable If Not RS.EOF Then strTable1 = RS("SomeRec") Else RS.ADDNEW RS("SomeRec") = tempRec1 RS.UPDATE RS.Requery strTable1 = RS("SomeRec") End If RS.CLOSE Select * from table2 where id =2 If Not RS.EOF Then strTable2 = RS("SomeRec") Else RS.ADDNEW RS("SomeRec") = tempRec2 RS.UPDATE RS.Requery strTable2 = RS("SomeRec") End If RS.CLOSE Select * from table3 where id =3 If Not RS.EOF Then strTable3 = RS("SomeRec") Else RS.ADDNEW RS("SomeRec") = tempRec3 RS.UPDATE RS.Requery strTable3 = RS("SomeRec") End If RS.CLOSE INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "' These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated. Thanks -Scott
View Replies !
Combine Multiple Rows Into One
Hello, I have a delima, and im not really sure if this possible. But i have a table like lets say id | data1 1 this 2 that 3 stuff i want to be able to return this as one row with the data from data1 in one column seperated by commas. so the result would be 1 Column this, that, stuff can anyone help me with this. Thank you, ~ Moe
View Replies !
Help: Need To Combine Multiple IF Queries
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below. /* will remove for aspx page use */ USE Database /* these params are on the page in drop down boxes*/ DECLARE @ProductID int; DECLARE @BuildID int; DECLARE @StatusID int; /* static params for this sample */ SET @ProductID = -1; SET @BuildID = -2 SET @StatusID = -3 /* the query that will build the datagrid. currently this runs and produces three different result sets. How do I combine these statements so they produce a single set of results? */ IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID)) IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID)) IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View Replies !
Combine Multiple Columns
Hi, I have the following query : select uname, count(ID) from tbh_Axis group by uname which works fine and displays Admin3 User18 How can i display the result as : Admin(3) User1(8) When I do this: select uname + '(' + count(ID) + ')' from tbh_Axis group by uname It doesnt work.
View Replies !
Combine Matching Multiple Rows Into One Row
IS there a way to combine all matching rows in a table so that itoutputs as one row, for example:tblMyStuffUniqueID int IDENTITYParentID intSomeSuch nvarchar(50)SomeSuch2 nvarchar(50)Table data:UniqueID ParentID SomeSuch SomeSuch21 1 Dog Bark2 1 Cat Meow3 3 Cow Moo4 3 Horse Whinnie5 5 Pig OinkDesired query result from Query:SELECT ??? as myText from tblMyStuff WHERE ParentID = 3myText = Cow Moo, Horse WhinnieHelp is appreciated,lq
View Replies !
Combine Multiple Results Of Subquery
Table users: userid, name, added... Table groups groupid, groupname... Table groupadmins: userid, groupid The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like: userid, name, groupids 12344, 'Bob', '123,234,345' If I try to just select groupid from groupadmins: select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
View Replies !
Combine Multiple RDL Files Into One RDL File
Hello, I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how? Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated. Thanks a lot. Let me know if the question is not clear. Mannu.
View Replies !
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
Please can anyone help me for the following? I want to merge multiple rows (eg. 3rows) into a single row with multip columns. for eg: data Date Shift Reading 01-MAR-08 1 879.880 01-MAR-08 2 854.858 01-MAR-08 3 833.836 02-MAR-08 1 809.810 02-MAR-08 2 785.784 02-MAR-08 3 761.760 i want output for the above as: Date Shift1 Shift2 Shift3 01-MAR-08 879.880 854.858 833.836 02-MAR-08 809.810 785.784 761.760 Please help me.
View Replies !
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
Need To Combine String Data From Multiple Columns Into One Column
When quering a table with given criteria, For ex: select notes, jobid, caller from contact where status in (6) and jobid = 173 I am getting this: This job will be posted to Monster for 2 weeks. 173 906 Waiting for full budget approval 173 906 TUrns out we're uppin 173 906 What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon? Please HELP!!!!!
View Replies !
Multiple Databases And Multiple Exe For A Single Solution
Dear Reader,Currently Am working on a Management Information System.Need to develop some part of the solution as almost Hard Coded Details: Both Front end and Database carry default valuse...which will never change in the near future. And some parts are depending on Changing rules ....so to be developed as separate exes...and separate databse are requires so ...Changes if needed can be adopted easily...Please guide ...How to manage abobe requirement?Please feel free to write for further clarifications.SuryaPrakash Paaatel--Message posted via http://www.sqlmonster.com
View Replies !
SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are: 1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup. 1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types. 1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately. I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that. Thanks in advance for any help/suggestions, Bob
View Replies !
Selecting Multiple Records By Multiple Filters...
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
View Replies !
Delete Multiple Records From Multiple Tables
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.) delete dbo.tblcase where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902', '299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860', '299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803') delete dbo.tblcaseclient where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902', '299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860', '299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803') delete dbo.tblcaseinformation where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902', '299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860', '299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803') delete dbo.tblcaselawyer where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902', '299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860', '299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803') delete dbo.tblcaseprosecutor where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902', '299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860', '299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
View Replies !
Selecting Multiple Records From Multiple Tables
i want to select all the user tables within the database and then all the records with in each table. plz tell me one query to do this. ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View Replies !
Single Or Multiple Sp ???
greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............
View Replies !
Bcp Single CPU Vs Multiple CPU
I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them. Is there something obvious that a single CPU box would run this much faster?
View Replies !
Handling SQL RollBack Transaction For More Records In A Single Process
I have over 500 transaction records in a single DB process handling within SQL transaction (Begin, Commit, RollBack and End). Is there any limitation for the following rollbacktransaction function to handle more records (eg. over 500 records)? Public Shared Sub RollBackTransaction() Dim transactionObj As Object Try transactionObj = SqlTransaction.GetExistingTransaction If (Not IsNothing(transactionObj)) Then CType(transactionObj, SqlTransaction).RollBack() End If Catch ex As Exception Throw New Exception(ex.Message) End Try End Sub
View Replies !
Passing Large Amount Of Records As Single Parameter?
Hello all - I currently have a project that has a gridview on the front end. The user can select multiple items from this grid, hit a button and all of the selected records should be updated in the process. However, this resultset can have a large amount of data coming back, and I'm stumped on how to pass all of the ID's to the sp. I'd rather not call the SP for each record selected, as there could be 1,000 items selected, and well, I'd rather not call the SP 1000 times :p. I thought of generating a comma delimited list as I'm looping through the grid and using dynamic sql, but the IDs are about 6-7 numbers long, and including comma, would take up almost all of the max space in a varchar. Are there any good solutions to this problem? Passing the items as an array? Generating a data table in .NET and passing that? Any help would be appreciated. -Jaime
View Replies !
Multiple Values For Single Row
hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...so far i created a table 'UserAccount' with column names as UserName as varchar(50)Password as varchar(50)EmailID as varchar(100)DateOfJoining as datetimeUserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..so now every user must have a list of other userid's.. as contact list..Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..THANK YOU !if it helps..iam using sql server express edition..and iam accessing using asp.net/C#
View Replies !
Single Row Into Multiple Rows
Hi All, We've a table as in the following format: PK_Column1 PK_Column2 Issue_Date1 Issue_Amount1 Issue_Category1 Issue_Reject1 Issue_Date2 Issue_Amount2 Issue_Category2 Issue_Reject2 We need to divide it into two new tables as follows: UniqueID PK_Column1 PK_Column2 And UniqueID PK_Column1 PK_Column2 Sequence_ID Issue_Date Issue_Amount Issue_Category Issue_Reject Unique1 1 Issue_Date1 Issue_Amount1 Issue_Category1 Issue_Reject1 Unique2 2 Issue_Date2 Issue_Amount2 Issue_Category2 Unique3 1 xx xx Unique4 2 xx xx Unique5 3 xx 4 xx There will be one UniqueID for each row. We'll get the uniqueID and PK1 and PK2 in a file. Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table. Can we do this without using cursors? This is going to be one time process. Any ideas are appreciated. Thanks, Siva.
View Replies !
|
TRACKER
