Select Many Records Into 1 Comma Seperated Field.

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


ADVERTISEMENT

Comma Seperated Field To Arrays For Each Record?

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

Field Contains Multiple Values Seperated By Comma

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

Comma Seperated Select

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

How To Insert Results Of A SQL Query Into The Field Of Another Table, As Comma Seperated Values.

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

Select Distinct On Field With Values Seperated By

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

SQL Comma Seperated List

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

Comma Seperated Values

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

Storing Comma Seperated Values

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

Script For Comma Seperated Values

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

Passing Comma Seperated Parameter To SP

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

How Do I Create An In-line Comma Seperated List

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

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 View Related

Need Sql To Return The Result Of A Query As Comma Seperated Values.

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

Inserting From A Comma-seperated Ascii-file With Logics

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

Splitting A Comma Seperated Column Into Multiple Rows

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

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 View Related

Help - How To Select From A Comma Delimited Field ?

Nov 2, 2003

Dear SQL,

Lets say I have a field: User_Names

and it can have more than one name
example: "Yovav,John,Shon"

How can I select all the records that has "Yovav" in them ?

I try some like this:
SELECT * FROM User_Table WHERE User_Names IN ('Yovav')

but it only works if the field User_Names only has "Yovav" in it with no extras...


is there some SQL function to tell if string is found in a field ?

Any hope 4 me ?

View 2 Replies View Related

SQL Server 2012 :: Expand Comma Separated Values In Field In Select Statement

Jul 13, 2015

Consider the following data:

create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')

if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.

View 9 Replies View Related

Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)

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

Select Records That Do Not Duplicate On A Certain Field.

Mar 15, 2004

I am looking for some help in pulling certain people out of a table

This is the basic setup of my table


PK ID NAME MEETING
1 11111 Joe CLASS98
2 22222 Jane CLASS98
3 33333 Bob CLASS98
4 11111 Joe CLASS04
5 22222 Jane CLASS04
6 44444 Sally CLASS04

What I am wanting to do is Select only the people who attended CLASS98 but didn't attend CLASS04

I could just remove the CLASS04 people by sorting if I could just return the rows that do not duplicate the ID and meeting is either CLASS04 or CLASS98

Either way would get me to my goal.

Any help is GREATLY appreciated.

Thanks
Jimmyjoe

View 11 Replies View Related

Need Help W/ SELECT From One Table, One Field, Multiple Unique Records

Apr 22, 2006

I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:

Anna Smith
John Doe

etc. I did not see IDs listed for any of the names in the table when I looked.

There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.

Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:

strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"

This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.

Is there a way to modify this statement to pull only the records that I want?

Thanks for any help you can give.

AJ

View 3 Replies View Related

T-SQL (SS2K8) :: TOP N Records Comma Separated

Feb 14, 2015

Below is my sample data and query

declare @Sample table (ID int, message varchar(1000))
insert into @Sample(ID,message)
select 1,'Testing 1' union all
select 1,'Testing 2' union all
select 1,'Testing 3' union all

[Code] ....

I need to get top three values has to be comma separated. for example id 1 has 5 rows message which comma separated. Instead i need to consider top three message group by Id

expected result :

IDmessage
1Testing 1, Testing 2, Testing 3
2Testing 6, Testing 7, Testing 8
3Testing 11, Testing 12, Testing 13

View 3 Replies View Related

Split Up Comma-delimited Field

Jun 7, 2002

I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

View 9 Replies View Related

Fast Way To Concat Records To Comma Sep VARCHAR

Jul 20, 2005

Hi All,Here's a challenge.If there is a 'one word' answer to this, i.e. the name of a built inSQL Server function I can use, that would be great! However...I have a standard 1:m relationship, e.g. tblHeader and tblDetail.I want to create a view of records from 'tblHeader' and within thatview have a column (called e.g. DetailTypes) that provides a singlecomma separated varchar of values from a varchar field (called e.g.DetailType) that appears in related records in the 'tblDetail' table.(hope you understood that :)e.g. the contents of my 'tblDetail' table could be....Detail ID | Header ID | DetailType-----------------------------------1 | 1 | A2 | 1 | A3 | 1 | B4 | 2 | A5 | 2 | C6 | 3 | BTherefore, the view I want to create should return:Header ID | DetailTypes-------------------------------1 | 2A, 1B2 | 1A, 1C3 | 1Bi.e. the first row can be read as "Header 1 has 2 'A' detail recordsand 1 'B' detail record."I have created a view e.g. 'vqryHeaders' which calls a user definedfunction that takes the HeaderID and opens a cursor on another view,e.g. 'vqryDetailGrouped'. The other view groups the records intblDetail so that I can get a count of each DetailType for each HeaderID. The cursor then loops through the returned records concatenatingthe count and detail type into a comma separated string (as shownabove).However, when I run this across 20k records it is soooo sloooooww. Ihave indexes on the relationship fields and I am using realisticallysized varchars - neither made any difference in speed. It isdefinately the function that I wrote that slows it down as the view islightning fast when I remove my function call.I can supply source code if necessary, but I think that this is akind-of generic problem so I don't see the point - yet.I really hope you can help.Regards,Jezz

View 11 Replies View Related

Insert Number With Comma Into SQL Database 'int' Field

Feb 8, 2006

How can I allow users to input numbers with commas into a database field with an 'int' datatype without getting this error, 'Input string was not in a correct format'?

View 3 Replies View Related

Normalizing Comma Separated String To Multiple Records

Oct 17, 2012

I need to normalise comma separated strings of tags (SQL Server 2008 R2).

E.g. (1, 'abc, DEF, xyzrpt') should become
(1, 'abc')
(1, 'DEF')
(1, 'xyzrpt')

I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.

Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').

If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.

View 2 Replies View Related

Splitting Out Names From One Field Delimted By Comma And A Space

Apr 28, 2004

have a field in a table that has combined lastname,firstname and middle name like

combs,albert mike
woods-athere,jane alice

The last and first name are separated by a comma, and the middle name by a space
I need in tsql to split them to last name,first name and middle name
I used below

select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname',
substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname',
substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename'
FROM Demographic_staging

I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space

View 3 Replies View Related

Createing SQL Data Base Record For Comma Delimted Records

Sep 15, 2006

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

View 6 Replies View Related

How Do You Parse A Single Field List Of Values Separated By Comma?

Jan 3, 2008


IE:
ID ContactID

1 4, 5, 6, 8
2 3,4,6

Someone coded their database like this. It is a SQL server table with these two fields.
How do I use SSIS to parse out that single field?

View 5 Replies View Related

SQL Server 2008 :: Turn Field Values Into Comma Separated List

Aug 11, 2015

I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.

Example 1:

field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g

and I would like to get it in the form

Example 2:

field1_field3
1_____a,b,c
2_____f,g

View 2 Replies View Related

Debug Stored Procedure That Uses Comma Delimited List To Insert Multiple Records

Jan 18, 2006

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
 
 

View 4 Replies View Related

SELECT WHERE IN Comma Delimited String

Jun 3, 2008

Hello

I have a table with column Options where each field contains a comma delimited list of ID numbers.

I want to select any records where a certain ID number appears in that list.

So something like:

SELECT * FROM Table t1
WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID)

But that gives me the error:

Syntax error converting the varchar value '39,20' to a column of data type int.

I feel I'm close though! Could anyone point me in the right direction?

Many thanks

Square

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved