Need Help In Combining Results ..

Oct 22, 2007

Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table  Table 2. How can I combine these two to get the third table, Table 3 ?   
SELECT     TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM         dbo.Orders
WHERE     (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
  
Table 1 Results 
EmployeeID   CountShipVia1




1

                    82


2

                    71


3

                    81


4

                    116


5

                    29


6

                    48


7

                    44


8

                    75


9

                    29                                                                        
SELECT     TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM         dbo.Orders
WHERE     (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID   
Table 2 results 
EmployeeID   CountShipVia2




1

                    44


2

                    36


3

                    45


4

                    70


5

                    15


6

                    25


7

                    24


8

                    48


9

                    19      
Table 3 the desired result:  
EmployeeID    CountShipVia1     CountShipVia2




1

                         82                      44


2

                         71                      36


3

                         81                      45


4

                         116                    70


5

                         29                      15


6

                         48                      25


7

                         44                      24


8

                         75                      48


9

                         29                      19
 
 
thanksrobby 

View 5 Replies


ADVERTISEMENT

Combining Queries/ Results

May 4, 2005

I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?

View 1 Replies View Related

Combining Multiple Results Into One Row

Mar 6, 2006

Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:

create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)

As you can tell, the 'custom_address' column is going to house the results of the combination.

I am trying to INSERT (combine) using this statement:

INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id

I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.

Then I do my update:

UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id

However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.

If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?

As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.

View 5 Replies View Related

Combining Results Into Columns

Oct 18, 2013

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

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23

I want to convert this to the following :

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23

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:

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23

View 1 Replies View Related

Combining Two Results Sets

Jun 3, 2014

i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?

View 5 Replies View Related

Combining Subquery Results Into One Field

Jun 19, 2007

Hello there
 I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms.  I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking.  Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query.  The table structures are as follow:
 bookingrequestion - bookingrequestid, startdate, enddate
roomsbooked-id, bookingrequestid, roomname
i'm basically trying to use the following query
select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms
i'd like the results of the subquery to return the room names as A,B,C.  I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.
 thanks
 
 

View 1 Replies View Related

Combining The Results Of A Cursor Loop

Aug 7, 2007

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @IdsString VARCHAR(255), @Id int


SELECT @IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id

FETCH NEXT FROM GetData
INTO @Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuile

View 14 Replies View Related

Combining Two Field Results From Same Table

Mar 19, 2012

My application saves customer email addresses in two different fields in ym table

How do i combine two fields from the same table in a select statement?

I've tried the following:

Select EmailAddy + ', ' + FriendsEmail AS Expr1
FROM dbo.Contacts

But all I get are the results from one field.

View 4 Replies View Related

Combining 3 Tables To Get Required Results

Jul 2, 2014

I have a quick question for the SQL community about how to combine 3 tables to get the results needed...

The table names are :
setup_zipcode,
setup_category,
and listing

and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude

and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name

and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country

I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...

So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..

How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?

I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...

$cat is assigned in a query previously on the page...

SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC

When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..

I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..

If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...

View 1 Replies View Related

Combining Results In Comma Delimitered Strings

Jul 26, 2004

I know this has been addressed before but I can't find it...

I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...

Anyone able to help?

View 14 Replies View Related

Combining Results Of Two Similar Queries Into One Result Set?

Mar 5, 2012

Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.

I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.

I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.

View 6 Replies View Related

Combining Two Queries Producing Unexpected Results

Mar 5, 2008

I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.


The following query returns, for each store, the total number of records in the ProductInventory table:

SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
GROUP BY Stores.Name

The following query returns, for each store, the total number of records in the Customers table:

SELECT Stores.Name,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name



I combined the two queries:

SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
LEFT JOIN Customers ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name

When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected


What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!

View 7 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

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:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 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
SELECT * FROM Customer WHERE
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.
Thanks,
James.

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

Thanks
dotnetdev1

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

BrianAustinHope83432230.7187500.7352941.15625014612201221

GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218

RobBennettMelville83029240.8275860.8333331.55172411904102117

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

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
 

View 3 Replies View Related

PASTE SQL RESULTS INTO EXCEL - Funny Results

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

Combining Databases

Jun 5, 2007

Dear Developres,
 
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.
at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)???
Thanks in advance.
 

View 5 Replies View Related

Combining 2 Sql Queries

Nov 22, 2003

hello everyone

there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :

select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5


and the other query

select x1, x2 from Table1 where table1.anything = 5

is there anyway????

Thank you

View 2 Replies View Related

Combining Tables...

May 9, 2004

Hello everyone,

I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.

I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:

If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:


CREATE PROCEDURE CMRC_Databases_DataTransfer
AS
DELETE FROM CMRC_ZipRegionTest

INSERT INTO CMRC_ZipRegionTest
(
Zip,
UpsRegion,
UspsRegion
)
SELECT
CMRC_ZipRegionUps.Zip,
CMRC_ZipRegionUps.UpsRegion,
CMRC_ZipRegionUsps.UspsRegion
FROM
CMRC_ZipRegion,
CMRC_ZipRegionUsps
GO


Is there any way to do this? Or do I have to manually enter all the entries?

Any help would be great. Thank you.

-Alec

View 1 Replies View Related

Combining Two Queries

Jan 23, 2006

I have a transactions table that stores prices for products bought and sold.

If I want average buying prices I  use:

SELECT  AVG(price), product FROM transactions WHERE transactiontype=1 GROUP BY product

and for selling prices:
SELECT  AVG(price), product FROM transactions WHERE transactiontype=2 GROUP BY product

Is there a way to combine this into one SQL query,  to create one bindable dataset ?

View 2 Replies View Related

Help With Combining Sql Statements

Mar 7, 2006

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName  = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.

View 2 Replies View Related

Combining Columns

Mar 3, 2000

I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.

Do anyone know the syntax??
Thanks, Vic

View 1 Replies View Related

Combining Records

Aug 27, 2007

Hi,
Can anybody please tell how can I combing all records in field into one field.
For example

If my table is like

Final
_______
aaa
bbb
ccc
ddd


and i want result as

final1
_____
aaabbbcccddd


I do not want to use cursors for this. Please let me know if somebody knows the answer

Thanks

View 2 Replies View Related

Combining Records

Dec 18, 2006

I have a database table tblobjects like this:

object_name, reference_id

a 1
a 2
a 3
a 4

b 2
b 3
b 1
b 4

c 2
c 4
c 5
c 6

d 2
d 4
d 5
d 6


I now would like to have a SQL query which gives me the number of
unique object and reference combinations, like this:

a
b
c

d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is
also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.

Any ideas how can I do this?

Thanks!

View 1 Replies View Related

Combining 2 SQL Databases

Dec 13, 2004

Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:

View 12 Replies View Related

Combining Two Queries

Apr 12, 2006

These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.

I hope that's clear; if not, maybe the SQL will make it clearer:


SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]

WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))

GROUP BY [tblTickets].[id]



SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]

WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))

GROUP BY [tblCombinations_Tickets].[ticket_id]


I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.

I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.

This has got to be laughably simple. But I'm stuck :( Can anyone help?

View 3 Replies View Related

'Combining' Fields

Jul 12, 2006

Not concatenation, more... err.. I don't know what you'd call it.


SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]


(Apologies for the table/col names, this is all very temporary)

So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.

At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.

What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.

Does that make sense?

View 3 Replies View Related

Combining Two Tables

May 15, 2007

If I have two tables with the following data:

Table_A
A
B
C

Table_B
1
2
3

is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

View 2 Replies View Related

Combining Two Rows Into One

Jul 23, 2014

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.

Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved