Extracting Multiple Values As Comma Seperated
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
ADVERTISEMENT
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
Nov 11, 2004
Hi all,
If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...
Code:
Contracts table
--------------------------------
Concode - description
--------------------------------
KIDD - Kidderminster General
UNIV - University Hospitals
and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...
Code:
controls table
--------------------------
Code - Contracts
--------------------------
BA - KIDD /UNIV /NWPCT
With the those tables and the columns like they are, how can I get my query to display the following information...
PHP Code:
---------------------------------------------------
Con(Controls table) - Description(contracts table)
---------------------------------------------------
BA - Kidderminster General
BA - University Hospitals
---------------------------------------------------
I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table
Tryst
View 7 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
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
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
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
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
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
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
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
Oct 16, 2007
Hello,
I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.
Thanks a lot for any feedback!
View 6 Replies
View Related
Jan 2, 2008
I have a field called "Owners", and it's a child to an "Activities" table.
An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.
I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.
I'm kinda stuck on how to do this.
Thanks!
View 3 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
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
Sep 11, 2004
hi all
i want to extract only the duplicate values from a table
can any one know the sql syntax for that
thnking u
jag
View 2 Replies
View Related
Sep 21, 2007
I need some help with the following...
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?
View 10 Replies
View Related
Jun 3, 2008
Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
Could anyone help me construct this query?
View 2 Replies
View Related
Oct 18, 2006
Hello,
I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.
My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.
I need help...does anyone know what's wrong?
View 4 Replies
View Related
Jun 23, 2006
I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.
My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.
View 5 Replies
View Related
Feb 27, 2006
Hi,
I have an access database that is storing text in english and chinese characters. I am importing the data using SSIS and it is putting a 'square' character in place of all the chinese characters. Can I associate multiple code pages to a control? If this is not possible, I would just like to Extract the data and Load it into a table, and i will just handle the errors and write the key fields to an error log. The problem is that the error handler is not redirecting the row. It just simply fails. I have changed all the error actions to Redirect Row too. I currently have a script checking if the 'square' character exists but i feel this should be handled a better way? Has anyone run into this issue or have any suggestions??
This is the error code i get
Data conversion failed. The data conversion for column "FDCDOPS0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
View 5 Replies
View Related
Nov 30, 2004
Hello, I need your advice.
Here's my scenario.
Table A
------------
id name
100 apple
115 grape
125 tomato
145 melon
Table B
-------------
id Fruits
11 100, 115, 145
12 125, 115
13 100
I thought i could get the list of fruits using this statement:
select name
from A where id IN (select fruits from B where id = 11)
But apparently not, it's working if
select name
from A where id IN (select fruits from B where id = 13)
That means it does not recognize values seperated by comma. Anyone who has any idea how to make it work?
Thanks in advance.
HS.
View 5 Replies
View Related
