I am running a query to pull data from 2 tables. However multiple data elements could be attached to one unique ID which when I run the query it repeats causing the entire data set to give inaccurate numbers. How to achieve this:
With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.
;WITH stored_procedures AS ( SELECT o.id, o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P') SELECT id,proc_name, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name
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.
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?
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
I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.
DECLARE @M31 ( M31_SQL_ID INT ,JOB_NUMBER INT ,LINE_NUMBER INT ,WORKS_DESC VARCHAR)
[Code] ...
Output should be as below
219242 16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back
219245 16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...
How to combine multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME, dbo.AccessLog.INOUT FROM dbo.AccessLog LEFT OUTER JOIN dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015') ORDER BY dbo.AccessLog.EMPLOYEEID
Hellow Folks. Here is the Original Data in my single SQL 2005 Table: Department: Sells: 1 Meat 1 Rice 1 Orange 2 Orange 2 Apple 3 Pears The Data I would like read separated by Semi-colon: Department: Sells: 1 Meat;Rice;Orange 2 Orange;Apple 3 Pears I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
Hi!I have a table looking like(username) (account number) (start date) (end date) (product)wich I can have up to 4 lines for the same client.I wist to transfert those lines into a new table looking like(username) (account number) (start date 1) (end date 1) (product 1)(start date 2) (end date 2) ... (product 4)How (in SQL) I could do it?
I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B
TABLE A
SampleCode Test Name
Result ID Name Sex
9300105 Peripheral Blood Film ....
a few poikilocytes are present. S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
No blast cells seen. S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
microcytes, elongated cells and S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
hypochromic but normocytic: . Some S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
Majority of rbcs appear slightly S7585512E DHANDAPANI MAHESH M
Output:
TABLE B
SampleCode Test Name
Result ID Name Sex
9300105 Peripheral Blood Film ....
a few poikilocytes are present, No blast cells seen.microcytes, elongated cells and hypochromic but normocytic. Some Majority of rbcs appear slightly S7585512E DHANDAPANI MAHESH M
Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a
Why does sp_helprotect throw an error if there are no special security permissions for a specified @username? Why not simply return a blank resultset? This makes the procedure a hassle to use when trying to capture results into a table as part of another stored procedure (use it for auditing).
[Edited]The same problem occurs when I execute xp_logininfo to get the list of members for a group that has no members. Instead of just passing back some sort of status, the procedure blows up and keeps me from scripting the procedure.
I am writing a database system which recieves information parsed from various data formats. These data formats may or may not be complete, and as such some rows in the database can have gaps. The input formats may contain reference to the same row (in this case the same Company record) but hold different facts about that company. Eg one message may have name, phone and fax, whereas another message may contain name, address and website. I need to be able to insert new companies into the database OR update current records with extended data as relevant, ive been looking for UPSERT or MERGE queries in SQL Server but i cant find any useful resources explaining its use. Alternativly i'd like to be able to condense potential duplicates into single rows, eg: NamePhoneEmailnullNamePhonenullWebsite which would combine into one row where the null values get set by values derived from other similar rows: Name PhoneEmailWebsite
Any help at all would be really appreciated Thanks, Toby
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables). I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z". The "All" part is very important.
For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.
I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".
I hope I am clear enough. Does anyone have any solutions???
We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd]. If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result. Below is how they designed the table and below that is the stored procedure to pull the records.
In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections]( [SectionID] [int] NOT NULL, [DocumentNo] [smallint] NULL, [SequenceNo] [smallint] NULL, [SectionNo] [smallint] NULL, [DocumentTypeID] [smallint] NULL, [SectionText] [ntext] NULL) Each paragraph of text (SectionText) is in its own row (SectionNo) Each primary document has a DocumentTypeID of 1 with three subdocument types (2=Index, 3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo: SELECT * FROM Sections WHERE CONTAINS (SectionText, 'exercise') ORDER BY DocumentNo For each row that contains the search term, I would like to return the full document (all rows as parapraphs within one row of returned data). In other words, I want to reconstitute the full document as it existed prior to being inserted into the database with paragraph separation. For exampe, if the search term is in row 3 of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of that document in one block of text that retains paragraph format (preferablly with a line break and carriage return between paragraphs). How can this be done?
As you can see its combine two rows of data group by id. Issue is If I use for xml path('') then its replace my html tags <b> to like <b> so after that then I again i need to replace all these symbols. Is it possible to get expected result without using xml path or if we use xml path then still my html tag leave as it is.
Consider the following: I have a table, say ORDERS, with these entries -
CustID ProductID 1 CAN 2 2 3 1,2 4 4 5 1,2,3,4,5,CAN 6 10 7 CAN 8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.
I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.
When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.
Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:
SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT FROM DW.CUST_SALE_ADDR WHERE (BUS_ADD_DT = ?)
The flat file destination was mainly used to confirm the select.
Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:
DELETE FROM DW.CUST_SALE_ADDR WHERE (BUS_ADD_DT = ?)
I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.
Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?
I am a little stomped and wandering if someone might have an idea howto go about doing this.following on from this guidehttp://www.4guysfromrolla.com/webtech/031004-1.shtml on matching acomma-delimited string, I would like to expand on this and match twocomma-delimited string in a sproc.In my database, table A have a city field containing a comma delimitedstring ie 'sydney, new york, chicago'. I am passing a similarcomma-delimited string to a sproc and returning matcheing id.so, we have table A:id/city1/sydney, new york, chicago2/new york, san antonio3/beijing, sydney4/london,beijingpassing string 'sydney, new york'need to return: id 1,2,3 (1,2 match new york and 1,3 matching sydney)any ideas?
I want to check for multiple patterns in a particular column. For one pattern I can write e.g. SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery e.g. (SELECT '%'+name+'%' FROM <TableName>)
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null drop table #left_table; if OBJECT_ID('tempdb..#right_table') is not null drop table #right_table; create table #left_table
Our division has approximately 300 employees. We have an annual shift bid where seniority is calculated using Date of Hire. If 2 or more employees share the same Date of Hire then we fall back to Date of Application. Currently the SSRS report does a very simple query and shows all the employees in order of their Date of Hire. If they match then it sorts the matching Date of Hire entries alphabetically by the employee name. It then becomes the task of the scheduler to locatethe entries with the same Date of Hire and manually look up the employees' dates of applications then sort them accordingly and re-write the report.
Goal: Convert the manual process into an automatic process by modifying the current SSRS report. Data: The dataset is "DivDir" which contains the following fields: "EmpName", "DofHire", & "AppDate".
EmpName DofHire AppDate Adam ...... 12/2/1996 11/15/1996 Bob ..... .... 1/16/1997 12/27/1996 Charlie ....... 1/16/1997 12/12/1996 Dan ...... ... 4/11/2001 3/22/2001
In the above example I want the SSRS report to list the employees in this order: Adam, Charlie, Bob, Dan.How do I do this programmatically using SSRS?
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))
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
