Hi world,Normally we receive the results of a query in several or thousands of rows. Select * From Clients-------------------------Row1. Client1Row2. Client2....Which is the the way to have everything on the same row separated by commas?Row1. Client1, Client2...thxDavid
View 1 RepliesI have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
hello dudes. i've got the following table:
table name: assassins
fields:
assassinid int, identity, primary key
assassinname nvarchar
assassincallsign nvarchar
knifekills int
sniperkills int
stealthkills int
fibergarotekills int
sample data:
1, Dana White, tapout, 12, 0, 12, 1
2, Chuck Liddell, iceman, 30, 0, 0, 2
3, Timothy Oliphant, FortySeven, 100, 20, 150, 30
supposed i want to retreive only those records whose type of kills is on a given list, how do i do that? the list is actually dynamic since the kill types is actually coming from a checkbox on my page:
[] Knife Kills
[] Sniper Kills
[] Stealth Kills
[] Fibergarrote Kills
supposed i will only check Knife Kills, it will only retreive those records whose knifekills > 0
or if i check Snipers Kills and Stealth Kills, then those records whose sniperkills and stealthkills is > 0.
any of you know how to do that?
U + U + D + D + L + R + L + R + Sel + Start...
I am new to SQL administration.[color=blue]>From a list of IDs that are the primary key in one table (i.e. Customer[/color]Table), I want to make changes in tables that use those IDs as aforeign key.Basically I want to say:If fk_ID is in list [1,2,3,4,5] thendo these statements to that recordEnd ifWhere do I begin?Thanks for help with this low-level view of SQL programming.-tom
View 8 Replies View Relatedhilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies View RelatedDear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. These are the values i need to update
old value new value
7007 4007
7008 4008
4008 7
7009 4009
7011 4011
4011 ' '
7010 4010
4010 1
7016 4016
4016 1
4506 4006
4512 4012
how do I write one query that will accomplish this?
Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output
select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate
[Code] .....
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
I have done queries before where you have something like this:
Select name
From people
Where uid in (Select UID from employees where salary > 500000)
as an example.
I have to use stored procedures, I can't connect directly...
I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records.
Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set
CheckedOut = 1
Where UID in (@UIDList)
I have tried a few different things, none works.
Any ideas how you might accomplish this with stored procedures?
The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that.
Thanks.
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150)
ASBEGIN
DECLARE @FileID INT
SET NOCOUNT ON
/*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
Select @FileID=@@Identity
/*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000)
SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
How can I do that?
Thanks.jsn
I need to create a stored procedure that takes a list of product
numbers(541, 456, CE6050,...) and selects only the customers that have
all product numbers in the list. I have a product table that has the
following schema:
rowid numeric(18,0),productNumber numeric(5,0),customerNumber numeric(5,0)
and a customer table:
customerNumber numeric(5,0),address varchar(50),contact varchar(50)
So a customer can have more than one product, but I need a select
statement that takes the product numbers and selects them against the
product table and returns only the customerNumbers that have the entire
list of product numbers. I have tried doing a join on the product list and productNumber, then join with the customer table on customerNumber, but that gets any entires found in the list not the entries that have all the product numbers. Thanks in advance for any help.
I have a table that has a list of skills, ie "HP One", "HP Two"
I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.
DECLARE @SkillSet NVarChar (200)
SET @SkillSet = '-HP One-,-HP Two-'
SET @SkillSet = Replace(@SkillSet, '-', '''')
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)
The following sp does not return any results, but when i set the last line to ..
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')
It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'
Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.
Thanks
Hi, i have a big problem. I´m having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm
Hi, I would like to create a Stored Procedure for obtain a list of insert using the information of a select queryI have to do X insert for days, using my start selectConstant : start dayx insert for each day if in my select I obtain 550 records and x = 100I've to do 100 insert for day with information of the selectplease note that in the final day I have to insert only 50 records and not 100 (500 and 100 are not mutiples) Thanks
View 13 Replies View RelatedI have a dropdown list that is populated by two columns in a database.select (firstname+surname) AS Fullname from table where id = 'id';
It works fine but i want to know how i would get a space between the firstname and the surname because at the moment all the values come back as JoeBloggs....without any spaces
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB
The following returns all base tables within the database of type "varchar":
Code:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns
WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb'
AND DATA_TYPE IN('varchar')"
AND TABLE_NAME IN(
SELECT TABLE_NAME FROM mydb.information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code:
select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME]
WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g.
SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title
HAVING COUNT(SubString(DiscNo, 0, 3)) > 1
SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle
ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist
ORDER BY Artist, Title, DiscNo
Drop TABLE #TempArtistTitle
GO
See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title.
SC8151-10 - Garth Brooks - Friends In Low Places
SC8125-04 - Garth Brooks - Friends In Low Places
LG5003-07 - Garth Brooks - Friends In Low Places
Could someone help me please?
i have a list of data as below:
Company Email
A AAA@abc.com
A BBB@abc.com
B xxx@def.com
i would like to write a query to list out as below where select the company A
AAA@abc.com, BBB@abc.com, ...
I want to create a column alias to represent the comparison of twocolumns (ie a boolean result of True or False). A simple example is:Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit[color=blue]>From VehicleFinance[/color]but I get an error 'Incorrect syntax near >'Books online states that the select_list can contain column_name orexpressionAn expression is a column name, constant, function, any combination ofcolumn names, constants and functions connected by an operator[color=blue]> is a binary operator[/color]So why do I get this error.Incidentally, if I use an arithmetic operator such as +, there is noproblem.
View 5 Replies View RelatedAccording to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
View 1 Replies View RelatedI have a report with a dataset/parameter to select the salesperson.
SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID
FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN
[DatabaseName$Sales Shipment Header] ON
[DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code]
UNION ALL
SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID
ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name
In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".
Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies View RelatedSELECT FriendName from Friends where RegionId = 23
I would like to create a comma delimited list of 'FriendName' column values in above query (example - Mike,John,Lisa,Emburey).
How would I do that?
How can I append a string to the field value in the select list
SELECT Code + '-20' FROM tb.....
I want to the above to return 2000-20 for example.
How can I do this?
Mike B
Hello there,
I have the following table:
ROOMTYPE AMENITY
========= =======
R001 1
R001 2
R001 3
R002 1
R002 2
R002 4
R003 1
Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned.
Can anyone help me on this?
Kush
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!
Hard to write a subject line to describe this one.
Anayway, I have a table with names and address plus an extra field noting a part number of product.
I'd like to build a SELECT string that will return one result for each name/address (uniques only in other words) and build a comma delimited field of all the part numbers for that name/address.
Example:
NAME ADDRESS PART NUMBER
John Smith 555 Main st., los angeles, ca 90003 5000
John Smith 555 Main st., los angeles, ca 90003 6650
Mike Jones 8569 West 18th Ave., San Diego, ca 1255
John Smith 555 Main st., los angeles, ca 90003 5144
Mike Jones 8569 West 18th Ave., San Diego, ca 2399
So I'd like my results to look like this:
NAME ADDRESS PARTS
John Smith 555 Main st., los angeles, ca 90003 5000,6650,5144
Mike Jones 8569 West 18th Ave., San Diego, ca 1255,2399
THanks in advance for any suggestions!
Raul
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