Hi ,I need to place the results of two different queries in the same resulttable parallel to each other.So if the result of the first query is1 122 343 45and the second query is1 342 443 98the results should be displayed as1 12 342 34 443 45 98If a union is done for both the queries , we get the results in rows.How can the above be done.Thanks in advance,vivekian
I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.
The query should have a surname column, a forenames column, and two id columns (from the person column of the table).
I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.
This is what i have done
select first.surname, first.forenames, first.person, second.person from shared.people first, shared.people second where first.surname= second.surname and first.forenames = second.forenames and not first.person = second.person order by first.surname, first.forenames
and i get results like this
Porter Sarah Victoria 9518823 9869770 Porter Sarah Victoria 9869770 9518823 - i.e. duplicates
I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.
Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:
Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
I am trying to take the results of a query and re-orient them into separate columns.
select distinct W_SUMMARYDETAILS.FACILITY_ID, W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year) W_SUMMARYDETAILS.FACILITY_NAME, W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year) From W_SUMMARYDETAILS full outer join W_DEF_SUMMARYDETAILS on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.
I have a sub report that returns a variable amount of results. On occasion many results will be returned and cause one or more extra pages to be created.
The results from my sub report are not very wide so what I would like to do is have the results roll over from the left hand side of the page to the right hand side of the page and then continue this pattern on sunsequest pages until all results are displayed. Very similar to newspaper columns.
Is this possibe? If so how can I go about doing this?
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2) EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script: SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added], dbo.cdbAttribInst.ainTVal AS Inudstry FROM dbo.cdbClient LEFT OUTER JOIN dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours FROM dbo.tblEventStaffRequired;
and SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked FROM tblStaffBookings GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode FROM TriggerTable FOR XML PATH(''), TYPE ).value('(./text())', 'VARCHAR(MAX)') ,1,2,'')
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains" 2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.