Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:
Table: Profiles
Fields: Id, ProfileName
Table: Groups
Fields: Id, GroupName, ProfileId
I then need to return something like this:
ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"
Does anyone know how this can be done?
Thanks!
This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:
SELECT * FROM Transactions
WHERE MyField LIKE (SELECT MyValues FROM SearchValues)
But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.
And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.
Any help would be appreciated-
Kenneth
Hey guys, I have a brain buster for you today:
I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255)
select e.ssn,
e.firstname,
e.lastname,
( @path = select coalesce(@path + ', ', '')
from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt
where s1.spaceID = 133225
select @path)
from employees e
where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
Hello,
I am trying to do something like this:
select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA
But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.
How do I do this?
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.
stocknumber transtype transsubtype balance
s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100
s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300
I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below
stocknumber in,deal out,cust in,cust
s01 300 300 100
s02 200 500 400
I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?
View 4 Replies View RelatedCREATE TABLE [dbo].[x1](
[nomer] [varchar](15) NOT NULL,
[tgl] [datetime] NOT NULL,
CONSTRAINT [pk_x1] PRIMARY KEY CLUSTERED
(
[nomer] ASC,
[Code] ....
Result :
nomer tanggal
12013-07-28 00:00:00.000
12013-07-29 00:00:00.000
12013-07-30 00:00:00.000
I Want to make :
nomer tanggal
1 28,29,30
Hi,
I have a table of users, a table of categories, and a many-to-many table linking users to categories.
My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.
Here is a stripped-down version of the table fields:
tbl_User
UserId, Email
tbl_Category
CatId, CatName
tbl_User_Category
UserId, CatId
I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:
DECLARE @list nvarchar(100)
SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
FROM tbl_User_Category
WHERE UserId = @UserId
SELECT @list as List
Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????
Any help would be fantastic!
thanks,
Rob
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
I am using xml schema that is like this:
<DetailRows>
<DetailRow>
<MonthNumber></MonthNumber>
<Amount></Amount>
</DetailRow>
</DetailRows>If my variable contains following xml document as un-typed xml
[Code] ....
However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Thanks,
Marc
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
I have a table like this :
ID Description ParentID Level
B.01 Parent 1 H
B.01.01 Parent 1.1 B.01 H
B.01.01.01 Parent 1.1.1 B.01.01 H
B.01.01.01.01 Detail 1 B.01.01.01 D
B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
Hi all:
In the Sql below, sample from William Pearson, the amount Spend is in descending order and the Rank number is in ascending order. Like this:
Spend Rank
24 1
12 2
10 3
9 4
What I wish to accomplish is:
Spend Rank (descending)
24 4
12 3
10 2
9 1
Please let me know what I need to accomplish it.
Thanks for the help
Victor
SELECT
CompanyName, Spend,
(SELECT COUNT(*)
FROM
ACC0704 AS CoSpendTotal
WHERE
ACC0704.Spend <= CoSpendTotal.Spend)
AS Rank
FROM
ACC0704
ORDER BY
Spend DESC
Hi all,
I've 3 tables COMPANY, COMPANY_SOFTWARES, SOFWARES. COMPANY_SOFTWARES table holds the data of the softwares used in the companies like;
COMPANY TABLE
COMPANY_ID COMPANY_NAME
------------- ----------------
10 My Company
COMPANY SOFTWARES TABLE
COMPANY_ID SOFTWARE_ID
------------- --------------
10 1
10 2
10 3
SOFWARES TABLE
SOFTWARE_ID SOFTWARE_NAME
-------------- -----------------
1 X
2 Y
3 Z
I need a result like;
COMPANY_ID COMAPNY_NAME SOFTWARES
------------- ---------------- ------------
10 My Company X, Y, Z
I've used a query like;
SELECT
COMPANY_ID
,COMPANY_NAME
,(
SELECT
SOFTWARE_NAME
FROM
COMPANY_SOFTWARES COS,
SOFTWARES S
WHERE
S.SOFTWARE_ID=CS.SOFTWARE_ID AND
COS.COMPANY_ID = C.COMPANY_ID
) AS SOFWARE_NAMES
FROM
COMPANY C
WHERE
AND C.COMPANY_ID = 10
But I get an error message;
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I've read that in MySQL there is CONCAT function for the result I want but I couldn't find any solution in MSSQL server, any solutions, suggestions?
Thanks.
i have two tables Student and Daily Attendance with following structure ,as i want to extract students of a class order by roll number which are not in Daily Attendance table , i know it will be solved by subquery but I don't know how to solve it.
Student:-
CREATE TABLE [dbo].[Student](
[Student_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Course_Id] [smallint] NULL, //Foreign key
[Class_Id] [int] NULL, //Foreign key
[Batch_Year] [varchar](20) NULL,
[Student_Initials] [varchar](20) NULL,
[code]....
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
Hi,
We are running sql server 2005 standard edition. In the query below, it has top percent clause in the subquery and returns various rows, 2, or 3, or 4 rows each times when running manually one after another. Does anyone have an explaination?
SELECT
dbo.WOB_STEP.STEP_UID
FROM
dbo.WOB_STEP
INNER JOIN dbo.CURRENT_DATES CD1 ON (dbo.WOB_STEP.CD_UID=CD1.CD_UID)
WHERE
( dbo.WOB_STEP.STEP_UID IN (SELECT TOP (50) PERCENT WITH TIES STEP_UID FROM dbo.vWOB_STEP_RANDOM WHERE OPERATOR_NAME = 'Jennelyn Llobrera' AND VENDOR_RETURN_FLAG = 1
ORDER BY RandomID) )
Thanks very much in advance !!!
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!
I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?
View 8 Replies View RelatedI need to query out multuple rows of data from the following table and retrieve it as a single row. Ideally, I'd like to do some sort of subquery that supports multiple columns, but as far as I know, that only exists on Oracle, not MSSQL.
Here's an example of the data:UserDefinedFieldId UserDefinedRowID FieldValue1 1 date (stored as text)2 1 text3 1 text1 2 date (stored as text)2 2 text3 2 text...27 UserDefinedFieldIds for each row in the actual table
I wrote a working query, but I need something more efficient for this particular application. (The query to do this must fit in a 2000char data field--with all 27columns brought in with separate subqueries, I'm pushing 5000.) Already tried shorter table names, which only saved me about a 1000 characters.
SELECT (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId AND SecondaryTable.UserDefinedFieldId = 1) AS Column1, ,,, (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId AND SecondaryTable.UserDefinedFieldId = 27) AS Column27,FROM UserDefinedData AS PrimaryTableWHERE PrimaryTable.UserDefinedFieldId = 5 AND Cast(Cast(PrimaryTable.FieldValue AS varchar(64)) AS datetime) > GetDate() ORDER BY PrimaryTable.UserDefinedRowID, PrimaryTable.UserDefinedFieldId
Any suggestions would be appreciated.
Thanks,
Brad
I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.
The statement has a select with an imbedded select and when I execute it I get the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Thia is a crude sample of the statement
SELECT ID + ( select * from tableB where TableB = 0 ) as result1
FROM TableB
Why am I getting this error and how do I fix the statement?
thanks
For deleting duplicate rows, i can use cursor and subquery.
cursor code
Declare dup_cursor cursor for
select acctnumber from LinkUrnABSADMBAR
group by acctnumber
having count(*) > 1
Open dup_cursor
Fetch Next from dup_cursor INTO @acctnumber
While (@@Fetch_Status = 0)
Begin
Delete from LinkUrnABSADMBAR
where acctnumber = @acctnumber
Fetch Next from dup_cursor INTO @acctnumber
End
Close dup_cursor
Deallocate dup_cursor
Subquery code
delete from galupload2000..test where id in (select id from galupload2000..test group by id having count(*) >1)
My question is which one is Better in performance????????????
Thanks
Sandipan
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies View RelatedI am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies View RelatedHi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuart
Table users:
userid, name, added...
Table groups
groupid, groupname...
Table groupadmins:
userid, groupid
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
Hi everybody,
I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.
Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'
Desired Result:
AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00
Table 1
RecordID Description
20 Whew
21 Hi
21 There
21 Good Morning
Table 2
Date AccountNo RecordID Amount
04/02/2006 220 19 80.0
04/03/2006 221 20 290.0
04/04/2006 221 21 728.0
04/06/2006 223 23 200.3
04/07/2006 225 25 2893.20
den2005
Hi,
I have two tables. Customers and Orders.
Customers table contains customer id, and customer name columns.
Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.
I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought.
Can I write this sql without using a subquery.
Thanks,
Chamal.
Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD
View 2 Replies View Related