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]
GO
Create table Indexes(indexname Varchar(100), index_Description
Varchar(100), index_keys Varchar(100))
GO
INSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'
GO
This will give me a table (northwind)
IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located on
SAMIS_SDM_Index FK_Shipped_Date
IX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located on
SAMIS_SDM_Data1FK_Insert_Date, FK_Insert_Time
As you see sp_helpindex will give me a comma seperated field. I want
to split the third column FK_Insert_Date, FK_Insert_Time into a extra
row
Like this :
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?
I am trying to return as a dataset of this that shows the ID as column 1 then each value in the 2nd column as individual columns if I use SELECT LEFT('2ndColumn,PATINDEX(',',2ndColum)-1) I can return the first value as a column but then can't return any further values individually after the first column, I am just learning the new functionality in SSIS so not sure whether this would be my answer as apposed to T-SQL, if anyone has any advice on this it would be greatly appreciated?
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK) WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK) WHERE P.PrsnPIISSN_Chr IN ( SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + '''' FROM MassProcessing_Rules PR WHERE PR.intRuleID = 10 )
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.
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?
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
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
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
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?
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.
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...
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!
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?
I have to split a column using comma a delimiter into multiple columns. I am able to do it if i know how many column will be present in the final output. But in daily run, the columns may vary randomly.
how to split columns without hardcoding how many columns it ll come.
This is the code am using
Code: WITH Split_Names (Fil_id,Name, xmlname) AS ( SELECT Fil_ID,
all the columns are separated with a "|" but the amount of columns are not fixed, so in lines 1 & 2 they are 4 columns and in line 3 there is 7 columns
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
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 ?
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.
I have the following table:id name1 yes2 no3 what4 is5 this6 niceThe amount of rows can vary from 1 to 50. I only need the name column.What SQL statement do I have to execute to get the following:yes,no,what,is,this,nice, (trailing , is acceptable)Thanks!
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
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
ID Description ParentID Level B.01 Parent 1 H B.01.01 Parent 1.1 B.01 H B.01.01.01 Parent 1.1.1 B.01.01 H B.01.01.01.01 Detail 1 B.01.01.01 D B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.
ID      Name                 State     Country 1      Scott, Ricky           NSW      AUS 2       Martin                  VIC        AUS 3   James, Peter,John    WA       AUS
ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below
ID      Name                 State     Country 1      Scott                    NSW      AUS 1      Ricky                     NSW      AUS 2       Martin                  VIC        AUS 3     James                     WA       AUS 3     Peter                      WA       AUS 3     John                      WA       AUS
