Get All The Matching Values In ColB And Display It Against ColA As Comma Seperated In The Same Row
Nov 6, 2007
Hi all sql gurus,
I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .
The tables below might give a better idea
current scenario:
Product Name SubmissionID columnC Column D
AAA 123
AAA 456
BBB 111
ccc 121
AAA 789
Expected result:
Product Name SubmissionID columnC Column D
AAA 123,456,789
BBB 111
CCC 121
Any suggestions/pointers would be highly appreciated.
View 5 Replies
ADVERTISEMENT
Nov 12, 2007
Hi all sql experts,
I have a table that has columns 1)ProductName and 2)qualification.
The reporting scenario is i have to display the highest qualification among all qualifications for the particular product name:
Suppose 3 qualifications(B,P,D)
D__lowest
B__Medium
P__highest
So i need P always if P is there among the group.
The different conditions would be
1)If the ProductName has only only type of qualification then display that.
2)If both B and D, then display only B.
3)If both P and D, then display only p.
4) If p,B and D then display only P.
The current table would be like:
ProductName Qualification Col3 Col4
P1 B
P1 P
P1 D
P2 B
P2 D
P3 P
P3 D
P4 D
P5 B
P5 B
P5 B
The expected Result:
ProductName Qualification Col3 Col4
P1 P
P2 B
P3 P
P4 D
P5 B
Any sort of help would be highly appreciated
View 3 Replies
View Related
May 24, 2008
I've got a table like so
date | number
___________|_____________
2007-01-01 | 43
2007-01-02 | 65
2007-01-03 | 23
2007-01-04 | 65
2007-01-05 | 23
2007-01-06 | 11
2007-01-07 | 52
2007-01-08 | 83
2007-01-09 | 44
and I want to get the most recent date in the past which I must go back where the sum of the numbers of the number column if greater than a certain number.
EG. From the above data, the date which I must go back to get a sum of 195 is 2007-01-05
as 44+83+52+11+23 is the least amount that is greater than 195.
Can anyone help, is this even possible? I'm flummoxed!
View 8 Replies
View Related
May 5, 2008
What is the best way? I have a field of comma seperated article type id values in a users profile. This list is a set of values that sows the article types the person does NOT want to see. Each article has an article type id. I need to do a select joining the article table to the member table that only shows those article id's that are not in the comma seperated list. How would I do that in a sql statement?
View 3 Replies
View Related
Mar 7, 2008
Hi I have a text field called testing which shows the selection the user has made from a checked list. I want to save the contents of testing to a datadase with coloum varchar and with a comma to seperate the selections. It stores ok but sql queries do not recognise them as comma seperated values. e.g the selection from the vb code produses: Beauty, Fashion, Travel. Sql takes the lot as one. When I test a query say select from table type where Category = beauy it gives nothing. How do I store it so sql would recognise it as seperate enteries.
my vb code is below
Dim msg As StringDim li As ListItem
msg = ""For Each li In CaregoryCheckBoxList.Items
If li.Selected = True Then
'asp has a security default of no accetping html in input fields br is html code so causes error
'msg = msg & "<br>" & li.Text & " selected."
msg = msg & li.Text & "," & " "
End If
Next
Testing.Text = msg
View 10 Replies
View Related
Jul 23, 2005
To get rid of redundant data in a table, my cleint will be providingsomething like this:IDtokeep Ids to delete34 24,35,4912 14,178,145754 32,65,68I have to write a script for each of the above rows which looks likethis:-----------------------------------update sometableset id = 34where id in (24,35,49)delete from sometablewhere id in (24,35,49)-----------------------------------As I said I have to do this for EACH row. Can I somehow automate thisor will I need to write to same script for each row (there are about5000 rows in this audit table)Any help is highly appreciated.Here is the DDL and inserts for the audit table.IF object_id(N'dbo.dataclean','U') is not nullDROP TABLE [dbo].[dataclean]GOCREATE TABLE [dataclean] ([IdTokeep] int NULL ,[IdsTodelete] varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )GOINSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(34,'24,35,49')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(12,'14,178,1457')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(54,'32,65,68')GO
View 3 Replies
View Related
May 3, 2006
Hi All
I'm really new to SQL and would appreciate a solution to the following problem.
I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.
C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202
I need a solution to select all values from CODES that are distinct so I end up with the following.
C200,C201,C202,C203,C204,C205,C206
Any help would be really appreciated. Please bear in mind I'm a complet novice.
View 4 Replies
View Related
Jul 17, 2007
Hi all,
This is my table :
WorkstationNo UserID
101 a1
102 a2
103 a3
101 a2
and there are many other fields too. The output I need is something like this
WorsktationNo OccupiedBy
101 a1,a2
102 a2
103 a3
In the similar fashion I would also require to retrieve the values based on the UserID something like this
UserID Workstations
a1 101
a2 101,102
a3 103
Could someone tell me how to write the query for this.
View 9 Replies
View Related
Jun 28, 2005
Hi, I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario... Table Name - Customer Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID JoinDate1 04/01/20052 01/03/20033 06/02/20044 01/05/20025 09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh
View 4 Replies
View Related
Mar 7, 2008
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.
For example, to explain things in detail:
create table dbo.phone_details
(country varchar(20),
state varchar(30),
list_of_toll_free_numbers text)
insert into dbo,phone_details
values
( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )
The final output I desire is:
country state list_of_toll_free_numbers
---------- ------- -----------------------------------------
USA CA 8009877654, 8665764398, 8776543219
View 8 Replies
View Related
Jul 26, 2005
Is there a way to return a comma seperated list in a query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.
View 4 Replies
View Related
May 14, 2008
Not sure the best way to do this. They are passing in a comma seperated string. I have a field that has comma seperated strings in it. I need to compare the 2 strings and do a select on that field that has any of the words in the string passed in. So if the pass...
"car,boat,van"
and in the field 2 of the 3 records get sent back
car,train,bike
boat,truck,plane
bike,plane,train
I would send them back the top 2 because the top one contains car and the second contains boat
View 5 Replies
View Related
Jan 21, 2008
Hi,
My SP is,
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber nvarchar(30),@StoreId nvarchar(500),@Year int)
AS
DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId1) AND ItemNum = @ItemNumber1'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@Count12=@Count OUTPUT,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId1) AND AltSKU = @ItemNumber1'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber = @ItemNumSKU
END
-- Creating table variable to have values from 1 to 12
CREATE TABLE #MonthSales (MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO #MonthSales VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END
--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'
-- putting ' for comma seperated storeid
SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId1) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber1'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year1'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN #MonthSales MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30),@Year1 int',@Status = @Status1,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@Year1=@Year
It is working If I am passing sigle storeId to the above SP. If I am selecting multiple StoreIDs from the report. It is not returning correct value. Can you please give me a suggestion?
Thanks
View 3 Replies
View Related
Oct 30, 2006
I want to pull out some information for documenting my user accounts.I want to be able to show all of the users and what companies those users have access to.
The table which stores the company information lists it in multiple rows, so there will be many results for each user
user -- companyid
johnd -- 2
johnd -- 5
johnd -- 1
I want to select this into one record so it will show up like this
user -- companies
johnd -- 2,5,1
The only way I know how to do this would be with a sub-query that uses a cursor to loop through & dump it into one string value, and then output the string value to the field. This seems extremely inefficient for such a seemingly simple task.
Can somebody help me out here?
thanks
View 3 Replies
View Related
May 12, 2008
Hello,
My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...
I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.
In the [Audit] table I can find all the field changes for the record in question using this SQL...
select *
from audit
where rowid = 1309606
order by auditID asc
My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...
audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'
audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'
audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1Â 3'
audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'
audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'
The funny character, , is ASCII 127.
So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.
What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?
Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.
Hope that makes sense and any help would be great!
Cheers,
Nick
View 6 Replies
View Related
Jul 20, 2005
Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?
View 3 Replies
View Related
Nov 17, 1999
Hi all !
I want to insert things to SQL-server from an ascii file but I also want to add logics with IF - Then _ Else statements. I guess the only way is to make a
stored Procedure.
How do you do when you want to read from a text file using the data into variables and then write it into the database ?
View 2 Replies
View Related
Jan 18, 2006
Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie
View 1 Replies
View Related
Jun 7, 2008
I have the following SQL Query:
SELECT user_profiles.userFirstName, user_profiles.userInitial, user_profiles.userLastName, user_types.userTypeDesc, user_profiles.userOfficeIDs, user_profiles.userEmail
FROM user_profiles
INNER JOIN user_types ON user_types.userTypeID = user_types.userTypeID
The field userOfficeID contains a comma separated list of values such as "1,2" to identify that the user is in both the NJ and NY office.
Table office_locations
OfficeID officeState
1 NJ
2 NY
3 CT
I would like the output to be something similar to:
Name Office userOfficeIDs value
John Smith NJ 1
Mary Smith NJ/NY 1,2
Jim Smith NJ/CT 1,3
Mike Smith NY 2
Any direction would be appreciated.
Thanks
View 19 Replies
View Related
Apr 27, 2015
I have one sp which has param name as cordinatorname varchar(max)
In where condition of my sp i passed as
coordinator=(coordinatorname in (select ltrim(rtrim(value)) from dbo.fnSPLIT(@coordinatorname,',')))
But now my promblm is for @coordinatorname i have values as 'coorcinator1', 'coordinato2,inc'
So when my ssrs report taking these values as multiselect, comma seperated coordinator2,inc also has comma already.
View 4 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
Mar 15, 2008
can u give me some idea how to make Sp who having two variables as a parameter having values seperated by ","
now thses vaues have to insert in to two tables tbColor .... colorname,product_id
and tbSize.......sizename,product_id
thanksss
View 11 Replies
View Related
Apr 5, 2006
I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma.
If I run the following command.
select distinct(DSCODES) from DB
The following is returned.
S100,S102,S103
S100,S103,S105
What I would like returned is the following
S100
S102
S103
S105
Is this possible?
Thank you in advance
Graham
View 2 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
May 6, 2015
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA :  abc,cde,efg  Â
ColumnB : cde,xyz,abc  Â
the result in  Â
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
View 5 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Feb 20, 2006
I apologise if this is a stupid question, but I would appreciated anyhelp on this subject.I want to create a view (VIEW_1 in example below) where I take numericvalues from a field in one table (DEPTH_FROM in TABLE_1) and find theclosest matching values from another field in another table (DEPTH_ATin TABLE_2) - the higher matching value and the lower matching value.TABLE_1BH_ID DEPTH_FROMB1 10.6B1 14.2B1 16.1B1 17.0TABLE_2BH_ID DEPTH_ATB1 9B1 10B1 11B1 12B1 13B1 14B1 15B1 16B1 17B1 18VIEW_1BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHERB1 10.6 1011B1 14.2 1415B1 16.1 1617B1 17.0 1717Any assistance would be appreciated.Regards,Hennie
View 2 Replies
View Related
Apr 25, 2008
Hi,
I have a table of parts which has a Tag column. The table is such that two parts can share a tag (but no more than two parts), essentially pairing them. I would like to use SSIS to match the parts based on this column value, so that I can add these into a new table.
For example, I could have:
(part1, tag-123, "a random part", serial123) and
(part2, tag-123, "another random part", serialxyz)
in the table, and would then concatenate the pair to enter into a new table, such that I have:
(part1, tag-123, "a random part", serial123, part2, tag-123, "another random part", serialxyz)
I'm new to SSIS and am unsure how I could achieve this, so if anyone could show me how I would appreciate it.
Thanks,
Simon
View 3 Replies
View Related
Apr 25, 2008
Hello, all.
I am new to SQL querying and I came across an issue while experimenting.
Say, I have two tables like the following, a Users table at the top, and a Roles table at the bottom (drawing the issue as I'm not fluent in English to explain):
(Users table)
UserId | RoleId | RoleOldId
-------+--------+----------
1......|x.......|11........
2......|y.......|22........
3......|z.......|22........
(Roles table)
RoleId | RoleOldId | RoleName
-------+-----------+-------------
10.....|11.........|Anonymous....
20.....|22.........|Superuser....
30.....|33.........|Administrator
The Roles table was changed to include a new column Roles.RoleId and the old column is named Roles.RoleOldId. Same is done for Users table.
Assume these are not foreign and/or primary keys. How can I fill Users.RoleId with new Roles.RoleIds, matching RoleOldIds at each tables? The resulting (x, y, z) set of Users table will be (10, 20, 20).
Thank you for your time.
View 3 Replies
View Related
Apr 2, 2015
I have tables in my database, tblNames1, tblNames2, tblNames3 for ex, and for addresses tblAddresses, can I match the address IDs in the names tables to the address table based on the values of a field? In this case CompanyName. Can they be matched automatically? My issue is that I have a huge CSV file with all the address information that needs to be assigned a foreign key based on the company name field which is also present in all the names tables, all the names are normalized.
View 1 Replies
View Related
Mar 22, 2008
I need to write a statement that returns the name, city, and state of each vendor that’s located in a unique city and state. In other words, I can not include vendors that have a city and state in common with another vendor.
Any help?
Thanks.
View 2 Replies
View Related
Aug 25, 2006
I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
View 10 Replies
View Related
May 20, 2014
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
View 4 Replies
View Related
