How To Merge Multiple Selects In One Select
Aug 22, 2013
I want to merge these queries in one query. When I use UNION ALL parameter sth_tarih sort is wrong.
SELECT TOP 5 sth_stok_kod,sth_evrakno_seri,sth_evrakno_sira,cha_kod ,sth_RECno,sth_tarih
FROM STOK_HAREKETLERI AS SH INNER JOIN CARI_HESAP_HAREKETLERI AS CHH ON SH.sth_evrakno_sira= CHH.cha_evrakno_sira WHERE sth_stok_kod = (
SELECT sth_stok_kod FROM STOK_HAREKETLERI WHERE sth_RECno = (SELECT MAX (sth_RECno) FROM STOK_HAREKETLERI) ) AND sth_evraktip = 3
ORDER BY sth_stok_kod ASC,sth_tarih DESC
[code]....
View 6 Replies
ADVERTISEMENT
Mar 21, 2014
I have two select statements:
SELECT COUNT(id) as 'clickthrough' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-clickthrough'
SELECT COUNT(id) as 'open' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-open'
They are identical except counting different eventtypes. There must be a better way to do this, so I can get both bits of data in one query.
View 3 Replies
View Related
Apr 29, 2008
Hallo, i need some help
I've done this script
SET NOCOUNT ON
DECLARE @login varchar(50)
DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)
OPEN cur
FETCH NEXT FROM cur
INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
But the result is not what i originally wanted.
It's generates multiple select tables with only one row.
I need only one table with all the rows generated.
I tried this:
SET NOCOUNT ON
DECLARE @login varchar(50)
DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)
OPEN cur
FETCH NEXT FROM cur
INTO @login
IF @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
WHILE @@FETCH_STATUS = 0
BEGIN
union
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
But it ended in an error on the union command.
Can you help me?
Thanks and sorry for my bad english :)
Debiru
View 6 Replies
View Related
Apr 14, 2008
Hi All,
I have a table that looks like this:
Date | Event
01/01/2008 | CRG
01/01/2008 | MAG
01/01/2008 | CPY
01/02/2008 | CPY
01/02/2008 | MAG
01/02/2008 | MAG
01/03/2008 | CRG
01/03/2008 | CPY
I need to display the data like this:
Date | CRG | MAG | CPY | Total
01/01/2008 | 1 | 1 | 1 | 3
01/02/2008 | 0 | 2 | 2 | 4
01/03/2008 | 1 | 0 | 1 | 2
Thank you so much for your help.
View 3 Replies
View Related
Jun 1, 2007
Hello, i would like to take the following select statements and make one table out of them. However, i want each statement to be its own column. The Union adds all of the statements into one colummn. Ultimately, i would like to place these into a stored procedure. Any help would be apreciated. Here are the statements:
select count(chalf_upd) as CHalfFile from elpc where elpc.chalf_upd is not null
select count (halfupd) as HalfFile from elpc where elpc.halfupd is not null
select count (cstaff_upd) as CStaffFile from elpc where elpc.cstaff_upd is not null
select count (staffupd) as StaffFile from elpc where elpc.staffupd is not null
select count (coffice_up) as COfficeFile from elpc where elpc.coffice_up is not null
select count (officeupd) as OfficeFile from elpc where elpc.officeupd is not null
select count (signupd) as SignFile from elpc where elpc.signupd is not null
select count (informupd) as ISignFile from elpc where elpc.informupd is not null
select count(staff_color_lastupd) as InStaffFile from staff_graphics_lastupdate where staff_graphics_lastupdate.staff_color_lastupd is not null
Strange Game. The only winnng move is not to play.
View 6 Replies
View Related
Nov 13, 2007
This is my statement:
SELECT TOP 10 * FROM Team_Roster ORDER BY P_Goals DESC
This is what I get:
Place Number Player Name Team Year Position Goals
1 44 Tyler, Bret Brunswick 2004 Webmaster 17
2 29 House, Tanner Bonny 0 5
3 25 Dee, Robby Cheverus 0 4
4 7 Marshall, Jeffrey Cheverus 0 3
5 27 Ramsey, Travis Biddeford 0 3
6 3 Thompson, Joe Brunswick 1960 Wired 3
7 5 VanDyk, Josh Cheverus 0 2
8 3 Dimmen, Jeff Cheverus 0 2
9 79 Danis-Pepin, Simon Cheverus 0 2
10 45 Oinker, Jeremy Biddeford 1999 Wonderboy 2
11 98 Good, Wicked Brunswick 2007 Lefty 2
12 21 Duffy, Matt Biddeford 0 2
What the heck is going on?
See the following list of: rows desired vs. rows returned
SELECT TOP n
When n=1, actual number of rows returned = 1 etc.
" 2 " " 2
3 3
4 12
5 12
6 12
7 12
8 12
9 12
10 12
11 12
12 12
13 16
14 16
15 16
16 16
17 27
Any ideas?
View 4 Replies
View Related
Feb 5, 2008
I need help in figuring out the proper way of writing a stored procedure out correctly to get my desired datasource. In my ocnIdToRatePlanOptions table, I will recieve a parameter via request.querystring @ocnId to filter out my result set for ocnIdToRatePlan table. Based on the ocnId filtered I want it to select the corresponding tables too.So, if a querystring is passed that is 3955 in my ocnIdToRatePlanOptions table, I want it to use it to create a select for RatePlan1. If a querystring is passed that is 1854 in my ocnIdToRatePlanOptions table, I want it to use to create a select for RatePlan2. Is this possible? ocnIdToRatePlanOptions Table [otrpoRefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[FKrpoRefId] [int] NOT NULL,1, 3955, 12, 1854, 2RatePlan1 Table[rp1RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL[fee] [decimal](18, 2) NOT NULL1, 3955, 1.002, 2350, 2.00RatePla2 Table[rp2RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[q_0_50] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_0_50] DEFAULT ((225)),[q_51_100] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_51_100] DEFAULT ((325)),[q_101_150] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_101_150] DEFAULT ((345)),[q_151_200] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_151_200] DEFAULT ((400)),[q_201_250] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_201_250] DEFAULT ((450)),[q_251_300] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_251_300] DEFAULT ((500)),[q_301_400] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_300_400] DEFAULT ((650)),[q_401_600] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_401_600] DEFAULT ((950)),[q_601] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_601] DEFAULT ((1.50)) 1,1854, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.502,8140, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.50
View 12 Replies
View Related
Dec 7, 2006
I have a parameter dropdown as USERID.
I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.
How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.
Thanks,
Kiran.
View 7 Replies
View Related
Jan 17, 2008
Here's my stored procedure:
ALTER PROCEDURE sproc_InsertOrder
(
@CustID tinyint,
@Size varchar(50),
@Crust varchar(50),
@total smallmoney
)
AS
INSERT INTO tblOrders (CustID, SizeID, CrustID, Total)
VALUES(@CustID,
SELECT SizeID FROM tblSizes WHERE @Size = SizeName,
SELECT CrustID FROM tblCrusts WHERE @Crust = Crust,
@total)
SELECT SCOPE_IDENTITY()
RETURN
And my tables:
tblOrders
-------------------------------------------
OrderID | CustID | SizeID | CrustID | Total
-------------------------------------------
tblSizes
--------------------------
SizeID | SizeName | Price
--------------------------
tblCrusts
------------------------
CrustID | Crust | Price
------------------------
My stored procedure is giving me an error, it says "Unable to parse query text", and I'm certain the problem is with the SELECT statements. What am I doing wrong?
View 4 Replies
View Related
Jul 20, 2005
I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location
View 6 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Jul 21, 2012
Recently i got one assignment from my senior, I have to merge multiple sql server databases,
Where to start?
How to start?
What prerequisites should consider?
What factor will cause error, how to tackle that error.
View 2 Replies
View Related
May 13, 2008
Hi,
I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3
NewsDetails
Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2
Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW
Thank you.
View 2 Replies
View Related
Nov 16, 2007
hi guys,
just wondering if there's a SSIS component out there some what similar to Merge join but can take up more than two inputs to join.
basically i have a big package with data sources coming from everywhere, they all have a unique column i can join on, so right now, i use merge join for every two sources, then join the output of that to another source so on and so forth. it would be easier if i can just join all of the sources in one component rather than putting a merge join for every single join. is there such a component out there, custom built maybe?
cheers
View 6 Replies
View Related
Oct 2, 2007
I have 1 publisher in MI and 3 pull subscribers (1 local subscriber in MI and 2 in NC).
I am trying to decide whether to use a central distributor or a distributor per subscriber. It is my understanding that if I use a central distributor, large transactions that are being process will block other transactions from happening and increase latency. Most of the transaction will be relatively small, but several times during the day clients update large amounts that cannot be predicted. I want to make sure the other locations are not waiting on a large update to be processed through the distributor.
My current plan is to create subscribers with their own distributor. What are the issues with doing that over a central distributor?
View 1 Replies
View Related
Nov 26, 2014
basically i have data like this
order_key comment
1 A
1 B
1 C
2 B
2 D
the data intends to be like this
order_key comment
1 A,B,C
2 B,D
View 3 Replies
View Related
Dec 24, 2014
I have a table that looks like this ...
idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222
I want to merge the data to look like this ...
idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)
-- Insert the records for type 1
INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts
View 2 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[Code] ....
View 7 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[code]....
View 2 Replies
View Related
May 13, 2015
I have a set of data which contains individual logon and logoff data. The table is as follows:
AGENTID, EVENTTIME, CURRENTSTATE
1234, 2015-05-12, 15 (For Logon) or 25 (For Logoff)
I'm hoping to extract this data as follows:
AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION
View 19 Replies
View Related
Jul 25, 2014
I am trying to build a SQL select query, but am struggling a little bit.I have a table with multiple inventory transactions, each with an ID and weight.
For example:
proc_packxacts
--------------
ID SysProg Weight
210359 271 986
210359 238 985
210359 61 984
210359 5 983
954679 271 1000
354852 271 568
354852 238 523
354852 59 521
354852 5 520
What I want to do is write a SQL query that returns records where there is a transaction for SysProg = 238, but return the weights for some of the other transactions in columns on the same row.Basically, from the above data set, what I would like to return is:
ID Weight (271) Weight (238) Weight (61 or 59)
210359 986 985 984
354852 568 523 521
How to start this?
View 2 Replies
View Related
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
View Related
Jul 23, 2014
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as
( Select ContactID from Contact where ContactID=@ContactID)
MERGE Employee as NewEmp
Using Cont as con
[code]...
View 2 Replies
View Related
Feb 19, 2015
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
View 1 Replies
View Related
Jun 9, 2007
is there anyway i can merge select statement and insert statement together?
what i want to do is select few attributes from a table and then directly insert the values to another table without another trigger.
for example, select * from product and with the values from product, insert into new_product (name, type, date) values (the values from select statment)
View 3 Replies
View Related
Nov 17, 2014
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
View 4 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Aug 25, 2015
SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
[Code] ...
View 5 Replies
View Related
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
Apr 3, 2008
I'm self-taught on T-SQL so forgive me if this is a dumb question. If I have a stored procedure containing the following...
IF [condition]
SELECT ...
ELSE
SELECT ...
Would it be more efficient/proper/etc. to break those two SELECTs out into their own SPs and execute them from the IF statement?
IF [condition]
EXEC [SP]
ELSE
EXEC [SP]
I've got a number of pretty lengthy SPs that I think are pretty tight but if I can make them more efficient by breaking them down into smaller tasks, I'd rather do that.
thanks!
Ron Moses
View 1 Replies
View Related
Apr 19, 2008
I've got two queries that I'm using, but I would like to know if I could combine them into one query.
SELECT MAX(ENTRY_ID) FROM MYDB.CALLS
.......
SELECT * FROM MYDB.CALLS WHERE ENTRY_ID = ?(result from previous query)
as always I appreciate any help.
thx
View 6 Replies
View Related
Jun 8, 2005
What is your opinion using Sub Selects versus Joining Tables?
SELECT (SELECT COMPANY_NAME
FROM COMPANIES
WHERE COMPANY_ID = c.COMPANY_ID) AS 'Company Name'
FROM COMPANY_ORDERS c
vs
SELECT co.COMPANY_NAME AS 'Company Name'
FROM COMPANIES c, COMPANY co
WHERE c.COMPANY_ID = co.COMPANY_ID
I'm not having any problems, just curious what everyone is
practicing and is "system-actically" more resourceful. I
personally like using Sub Selects (or Sub Queries) so this way I
wouldn't have to deal with complex joins and where conditions,
especially when your just dealing with a releational table.
View 5 Replies
View Related
Oct 16, 2007
how can i make this stored procedure:
INSERT INTO tableX (field1, field2, field3) VALUES (value1, value2, value3)
where value1 is field11 in table11
and value2 is field22 in table22
and value3 is field33 in table33
so all values are stored in another tables
View 1 Replies
View Related
