I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:
Original Data key1 key2 647942600014 647942285437 2324662490640 2324662285437 2324662066128 2222 2285437 2222 1111111 9999 1111111 9999 2222222
I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.
I tried playing with self joins between the tables but have had no success.
I am using Sql Server 2008 and the number of records could 500K to 1MM.
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
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.
Hello, 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'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?
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
In my site, when a user registers, I need to create rows in additional tables besides aspnet_Users. So, I need to be able to pass the generated userId guid to subsequent SqlCommands. I'm having a terrible time with this. What's the correct way to set up a SqlParameter so that it will accept a guid? I keep getting this error: "Conversion failed when converting from a character string to uniqueidentifier." I've tried creating the parameter both with and without a SqlDbType. cmd.Parameters.AddWithValue(paramName, guid); and SqlParameter p = new SqlParameter(paramName);p.SqlDbType = SqlDbType.Guid;cmd.Parameters.Add(p); and I get the same error either way. Driving me nuts! Any help appreciated.
This is stupid, I used to be able to do this all the time by mistake now I can't do it on purpose I want to be able to return a full list of matching records when only one is true LikeRow 1, ID_1, falseRow 2, ID_1, falseRow 3, ID_1, trueRow 4, ID_2, falseRow 5, ID_2, trueRow 6, ID_2, false I currently getRow 3, ID_1, trueRow 5, ID_2, true
I am in need of help to develop a query I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me? Lets see an example tb_exame
id_exame date_exame
1 07/01/2006
2 08/01/2006
3 09/01/2006 tb_exame_lab
id_exame_lab id_exame desc_exame status_exame
1 1 NORMAL 1
2 1
0
3 2 NORMAL 1
4 2 PROBLEMS 1
5 2 OK 1
6 3 OK 0 in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab can anyone help me?
I'm using this to find duplicates where a person has the same email but varying firstname and lastnames:
select distinct t1.booking_id, t1.first_name, t1.last_name, t1.email_add, t1.booking_status_id from [aren1002].[BOOKING] as t1 inner join [aren1002].[BOOKING] as t2 on t1.last_name=t2.last_name and t1.booking_id<>t2.booking_id where t1.booking_status_id = 330 order by last_name asc
Sample data: 3927 Greg Smith greg@emailno1.com 303 5012 John Smith greg@emailno1.com 303 6233 John Smith greg@emailno1.com 303 4880 Dulcie Abuud dulcie@theiremail.com 303
However it is listing the non duplicate rows, For example: The record with Abuud as the last name, doesn't have any duplicates in the table, so I don't want it listed.
The data should be like this: 3927 Greg Smith greg@emailno1.com 303 5012 John Smith greg@emailno1.com 303 6233 John Smith greg@emailno1.com 303
Line 1 Name: George Addresss date raise 125254 Test 10 125254 Test 10 125254 Test 10 125254 Test 10
Total: 80% 40
Name: Mik Addresss date raise 125254 Test 10 125254 Test 10 125254 Test 10
Total: 70% 30
Name: Jonathan Addresss date raise 125254 Test 10 125254 Test 10 125254 Test 10
Total: 70% 30 Line 10
Line 15 Total for this division: 33% ((100 )/3) 100
Department:B ¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦.. Here is what I did I have a list control that is grouped by department. this will get me the info from Line 1 to Line 10 then this list control is actually under another list control that is grouped by department to get the info from Line 15. I used this example for simplicity and what I am doing is fairly similar to this situation. My problem is, I need to find away to know how many totals I have to count the total for the entire department. In other words : from the example above how can I know I have to divide by 3 to get 33%. I can get the 100 without any problem but I cant tell what number I need to divided by. I tried to use Count(Name.Field) I was hoping this will give me 3 which indicates the divided number. But I was getting 7 for some reason. How can I get the number of times the list control was implemented for one department which is in this case 3?
I have written a reporting application which has a SQL2005 backend. An import routine into SQL, written by a 3rd party, frequently fails. The main problems are missing rows in certain tables.
I am going to write an SP that will accepts a from and to date. I then want to search for rows of type X between those dates that do not exist so we then know between a date range, we have no data for these XYZ days.
I have this working by returning all rows between the dates into a dataset, sorted by date, and then running through the rows and testing if the next rows date is the next expected date. This works but I think is a very poor solution. This is all done on the client in C#.
I want to learn and implement the most efficent way of doing this. My only solution in a SP was to make a temporary table of all dates between the date range for row type X and then do a right outer join against the data table, returning all rows which are missing.
Something like this:
SELECT twmd.date FROM #temp_table_with_all_dates ttwad RIGHT OUTER JOIN table_with_missing_date twmd ON ttwad.date = twmd.date WHERE twmd.date IS NULL
Would this be a good, efficent solution, or should I just stick to my processing of a dataset in C#?
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID] , MAX([TimeStart]) [LastRunDate] FROM [dbo].[Catalog] [c] LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID] WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source! group by [c].[Name],[c].[ItemID],[xl].[ReportID] order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT * FROM [dbo].[Catalog] WHERE [Type] NOT IN (1,5) -- Not a folder or a data source! AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1' select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
i want to break 2 by 2 rows in reportceiling(rownumber(nothing)/2).i used this expression in row group.Β URL....but i want to use this expression in matrix and that matirx is with in list . so i Β am getting error .Β how to use rownumber in list.here i used list to break the page wise id
