Results Doubling Up In Two Columns?

Aug 12, 2014

Why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

My query looks like the following -

declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
if @whCode = '02'
set @binLoc = 'S%'


why my results are doubling up in the two indicated columns.

View 1 Replies


T-SQL (SS2K8) :: Results Doubling Up In Two Columns?

Aug 12, 2014

I have written some SQL that relates to the (partial) result set below -

I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

My query looks like the following -


declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'


View 1 Replies View Related

Combining Results Into Columns

Oct 18, 2013

I have a query (SELECT * FROM Pricing) that produces the following results


I want to convert this to the following :


We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:



View 1 Replies View Related

Results In Parallel Columns

Jun 14, 2006

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

View 7 Replies View Related

Aggregation That Results Two Columns

Mar 12, 2007

I have following data coming from prior transformations.

Id Unit Name NewFlag
___ __________ ____________

1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0

and so on.

I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.

Just wondering if it is possible by using Aggregate and how.

Thanks for your help in advance,

View 1 Replies View Related

Search And Results Using Multiple Columns

Feb 10, 2004

I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)

I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.

I'm lost on both the search and the results code.

I'd like to have a drop down for the search like this:

<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>

Show all Vendors who are: <Select name="?????">
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
<Input type="submit" Value="Go">

Any ideas on how to make this work would be greatly appreciated.

View 3 Replies View Related

Duplicate Results In 2 Columns But Reversed

Feb 17, 2004

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


View 5 Replies View Related

Adding Columns To A Query From Results

Apr 14, 2014

I have a table called approval_levels in which I have 2 columns:

|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |

I have a second table called requests, in which I have 3 columns:

|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |

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.

|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |

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:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |

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?

View 1 Replies View Related

Need To Comparte 2 Columns For Non Matching Results

Mar 12, 2008

I have 2 columns from 2 separate tables.

Col1 is AccountName from tblOpportunities
Col2 is BillToName from AccountCodes

I am trying to get the results from tblOpportunities.AccountName that DO NOT also exist in AccountCodes.BillToName


View 3 Replies View Related

Incorrect Results From FTS On Multiple Columns

Apr 24, 2006

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

View 3 Replies View Related

SQL 2012 :: Results To Show Up As Separate Columns

Aug 22, 2014

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.



View 3 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

Feb 25, 2015

I am trying to take the results of a query and re-orient them into separate columns.

select distinct
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
full outer join W_DEF_SUMMARYDETAILS

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.

View 9 Replies View Related

Pivot Results With Columns For Movements In Period

Mar 7, 2014

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.

ts.loc_total_on_hand [Stock],
ts.loc_code [Depot],
po.qty [On Order], [Order Depot],

[Code] .....

View 2 Replies View Related

Trouble Putting Sub Report Results In Columns

Apr 30, 2008

Dear all,

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?

Many thanks in advance,

Trevor Keast.

View 1 Replies View Related

Sort Results By Rank Using FREETEXT On Multiple Columns

Apr 20, 2008

Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns.  Is there a way to do this? My two colums are:title and description
can anyone give any code snippets?

View 1 Replies View Related

Query Names Of Stored Procedure Results Columns?

Mar 2, 2012

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.

View 1 Replies View Related

SQL Server 2012 :: View Results Not Displaying Nil Columns

Sep 5, 2014

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?

SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN

[Code] ....

In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...

View 8 Replies View Related

UPDATE A Table With Aggregate Results For Multiple Columns

Jan 8, 2007

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 )

View 2 Replies View Related

Transact SQL :: How To Join Results Of Two Queries By Matching Columns

Aug 10, 2015

I have two queries as below;

SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;

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?

View 4 Replies View Related

T-SQL (SS2K8) :: Combine 2 Selects - Insert Results In Two Columns Of New Table

Sep 11, 2014

DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)

SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'

SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')

This is part of a bigger project but I am stuck on this part. I get back 2 result sets

Data Data
101 2013
102 2014

I want to insert the results in a new table 2 columns and get the results below.

New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014

View 2 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.


I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

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
).value('(./text())[1]', 'VARCHAR(MAX)')

[Code] ....

View 1 Replies View Related

Select Query Results In Multiple Columns Based On Type From Another Table

Apr 6, 2008

Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:

Miles Table:
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3

Miles Type Table

I'd like the results to be like this:

Date/Car/City Miles/Highway Miles/Off-Road Miles

Anyone? Thanks in advance!

View 3 Replies View Related

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

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!

View 12 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

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


Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007


I am running into an issue with RS2k PDF export.

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

View 1 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

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! 

View 1 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

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
CountyId IN
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

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.

View 1 Replies View Related

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008

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 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..


View 4 Replies View Related

Mind-boggling Gridview Results! Different Results For Different Teams..

Jun 18, 2008

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.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON = 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


GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218


AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

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)*** 

View 2 Replies View Related

Removing Individual Results From A Paged Set Of Results.

Oct 19, 2007

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.

View 3 Replies View Related


Jan 30, 2008

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.

View 6 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

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.


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?

CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Copyrights 2005-15, All rights reserved