I have a question, what does a statement look like that finds the duplicate rows and combines them,
I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number.
I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine.
Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish.
I have the following SQL statement:
SELECT part_number,
COUNT(part_number) AS NumOccurrences
FROM Products
GROUP BY Part_number
HAVING COUNT(part_number) > 1
I have two tables that I UNION to retrieve data for users. A combination of these should have only one employee in the table. The problem is there is a unique id created for the position of instructors. In the other table, it holds all employees with an employee number. Some data such as username, email address, etc., does not change. So even though UNION should remove duplicates, I still have duplicates because of usernames is what I'm filtering on, it is the same in each table. In the combined table I'm only selecting specific employees based on Job class and Job code. For employee id in the first table it is preceeded with 'B', and the second by 'T' (this is only to identify which table the data is taken from). Here is what I am getting when I Union both tables.
query SELECT distinct 'B-'+ Employee_IDÂ as Employee_ID , Username ,EmailÂ
I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
I've seen a number of questions on combining rows, but not one exactly like this. I have a solution, but I'd like to know if there are other ways. I'd like to select and combine rows from a table. Here's a simplified version of the table: tab1 key date status 1 1/1/06 stat1 1 1/2/06 stat2 1 1/3/06 stat3 1 1/4/06 stat4 2 1/1/06 stat1 2 1/2/06 stat2
And the desired results: key date status prevstatus 1 1/1/06 stat1 null 1 1/2/06 stat2 stat1 1 1/3/06 stat3 stat2 1 1/4/06 stat4 stat3 2 1/1/06 stat1 null 2 1/2/06 stat2 stat1
Here's the simplified version of the solution: select a.*,b.status prevstatus from tab1 a left join tab1 b on a.key = b.key and b.date = (select max(date) from tab1 c where a.key = c.key and a.date > c.date )
So the query: SELECT ConfigID, (SELECT ItemValue WHERE ItemTypeID = '1') AS Model, (SELECT ItemValue WHERE ItemTypeID = '3') AS Minor, (SELECT ItemValue WHERE ItemTypeID = '2') AS Customer FROM config c, item i WHERE ConfigID = '14583' AND c.ItemID = i.ItemID
Produces the result: ConfigID Model Minor Customer 14583 123 NULL NULL 14583 NULL NULL ABC 14583 NULL 789 NULL
How do I change the above query to get one row: ConfigID Model Minor Customer 14583 123 789 ABC
I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?
I am working with a database derived from text documents. One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column. I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result). How do I combine the rows with the same DocumentID into a single row of text data? This will put the entire text content of each document in its own row.
TEXT001 table as it is
DocumentID
SectionNo
SectionText
1
1
Paragraph 1 of Document 1
1
2
Paragraph 2 of Document 1
1
3
Paragraph 3 of Document 1
2
1
Paragraph 1 of Document 2
2
2
Paragraph 2 of Document 2
New TEXT table
DocumentID
SectionText
1
Entire text of Document 1
2
Entire text of Document 2
I realize that I can use €œunion€? to combine tables with the same data type, but that is not what I am trying to do. Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command. If anyone can tell how to do this, I would appreciate your help.
More modestly, I tried to use the €œGroup By€? clause to combine the SectionText data using this query:
SELECT DocumentID, SectionText FROM TEXT001 GROUP BY DocumentID
And got this error message:
Msg 8120, Level 16, State 1, Line 5 Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be €œsummed€?, so I tried including it in the GROUP BY clause:
SELECT DocumentID, SectionText FROM TEXT001 GROUP BY DocumentID, SectionText
And got his error message:
Msg 306, Level 16, State 2, Line 5 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag 1111296.54 3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id 100000001 200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag 1111296.54 3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id 100000001 (Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs) IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS ( SELECT 1234,'04/02/2014','Date' UNION ALL SELECT 1234,'123','Department' UNION ALL SELECT 1234,80.0,'Rate' ) SELECT * FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD 123404/02/2014Date 1234123 Department 123480.0 Rate
The desired results would be:
PERSONDate Department Rate 123404/02/2014 123 80.0
How I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together.
I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.
00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames 887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID, dbo.tblAttributes.AttributeName FROM dbo.tblProgramAttributes INNER JOIN dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%') ORDER BY dbo.tblProgramAttributes.ProgramID DESC
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
I used the following select statement to get duplicate records on Case_number column
select cases.distinct case_link, cases.case_number from cases group by case_link having case_number > 1
I got the error message that
"'cases.warrant_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. AND cases.case_number' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Any idea on a better statement to use. THANKS FOR YOUR HELP!
Hi, I have a table and this is what i did to get the desired result
Select A.col1,count(A.col1) from Tab1 group by col1 having count(A.Col1) > 1
i tried this - but it didnot worked - it returned col1 as blanks - Select A.col1,B.Col2,count(A.col1) from Tab1 A, Tab2 B where A.col1 = B.col1 group by A.col1 , b.col2 having count(A.Col1) > 1
As I was looking for all the rows that are apperaing more than once.
Now - The problem -
I have to join this table to another table Tab2 to get the other details. My Tab2 is a table from where I have to pull the Customer DEtails like name,address etc. How should I write this query? Any thinuhts? TIA
Hi. I'm a SQL Server newbie, very experienced with Access, developing an ASP.NET database editor web app. I query the database with a statement more or less in the following form:
SELECT organisation.OrgID, organisation.Name, organisation.whatever FROM services INNER JOIN servicegrouping ON services.serviceID=servicegrouping.serviceID INNER JOIN organisations ON servicegrouping.OrgID = organisations.OrgID WHERE services.service=x OR services.service=y
In other words, I have a database of organisations. The services offered by the organisations are in a separate table, and I only want to return organisations that offer services X or Y.
Okay, now if I did this in Access, this query would return just one record for each organisation that meets the condition, unless I was to include a field from the services table in the SELECT clause, in which case of course I would get one record for each organisation and unique service offered.
But in MS SQL, the query returns duplicate rows if there is more than service offered by the organisation that meets the WHERE condition (=x or =y). Why is this and what do I need to do to my SQL statement to ensure I only get unique rows?
I've a query which gets a set of data from multiple tables -
select * FROM A inner JOIN q ON (RIGHT(q.name,CHARINDEX('-',REVERSE(q.name))-1)= a.id) inner JOIN t ON (t.id = q.id) inner JOIN s ON (q.name = s.name ) inner join l on (s.name = l.name and t.name = l.name)
WHERE A.id = 764 and s.name = '764'
I get repeated # of rows for each id. I've some 136 rows for each q.id ( there are 6 q.ids and hence I get 816 rows instead of 136) These 136 rows are actually divided among thse q.ids as
I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten. There is no Primary Key in the csv file that can be used. I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates. When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want. Any thoughts as to why this needs to be run twice? Or is a better approach available? Here is my code - SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*) INTO temppkgactions FROM pkgactions GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1
DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 ) FROM dbo.pkgactions DROP TABLE temppkgactions
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. Randy Garland
Hi, I am encountering a problem. There are lots of duplicate rows in the cobol flat files (due to improper data entry and missing columns values )from where I am transforming data to sql 7. 0 tables using DTS. After transformation , can I some how mark the duplicate rows ? it is not for the purpose of eliminating them, but to enter the missing values and make all the rows complete and unique. I have the transformed table as a temporary table. Can I add a column like 'status' etc.. and have the column values marked '1' for the repeating rows etc.... Can anyone suggest 'any' possible way of implementing it ? Thanx Nisha
I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error.
The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows
select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99'
If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time.
