Data Row Comma Separated Cell To Many Rows
May 13, 2008
Hello!
We are on SqlServer 2005.
Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.
I have some data that is given to me that has two columns (below is for an example):
Column A is an identifiying number, i.e. for a project
Column B is a comma separated list of account strings for the project
A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):
AA.ProjectBuildTower ----- 2222, 3333, 4444, 5555
BB.ProjectBuildFence ----- X900, 6789, 9000, 9876
What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so:
AA.ProjectBuildTower ----- 2222
AA.ProjectBuildTower ----- 3333
AA.ProjectBuildTower ----- 4444
AA.ProjectBuildTower ----- 5555
BB.ProjectBuildFence ----- X900
BB.ProjectBuildFence ----- 6789
BB.ProjectBuildFence ----- 9000
BB.ProjectBuildFence ----- 9876
Any suggestions would greatly help!
Thanks!
View 5 Replies
ADVERTISEMENT
May 13, 2008
Hello!
We are on SqlServer 2005.
Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.
I have some data that is given to me that has two columns (below is for an example):
Column A is an identifiying number, i.e. for a project
Column B is a comma separated list of account strings for the project
A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):
AA.ProjectBuildTower ----- 2222, 3333, 4444, 5555
BB.ProjectBuildFence ----- X900, 6789, 9000, 9876
What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so:
AA.ProjectBuildTower ----- 2222
AA.ProjectBuildTower ----- 3333
AA.ProjectBuildTower ----- 4444
AA.ProjectBuildTower ----- 5555
BB.ProjectBuildFence ----- X900
BB.ProjectBuildFence ----- 6789
BB.ProjectBuildFence ----- 9000
BB.ProjectBuildFence ----- 9876
Any suggestions would greatly help!
Thanks!
View 7 Replies
View Related
Aug 21, 2007
Hello!!
First of all, thank you in advance for helping me!!!
My problem is that I have a BBDD with a table like this (in Oracle and in MySql, both)
E.g.
+---------+-----------+
| groupId | serviceId |
+---------+-----------+
| grup1 | service1 |
| grup1 | service2 |
| grup1 | service3 |
| grup2 | service1 |
| grup2 | service2 |
+---------+-----------+
And I need to do a select o a procedure or something that returns me something like this:
+---------+------------------------------------------+
| groupId | serviceId |
+---------+------------------------------------------+
| grup1 | service1, service2, service3 |
+---------+------------------------------------------+
Is this possible in any way????
Than you very very much.
Almu
View 5 Replies
View Related
Jul 4, 2004
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
View 4 Replies
View Related
Apr 19, 2013
I have an requirement where i need to show Employee Table and CustomerMeta Table joins In CustomerMeta Table (CustID)
Reference to Employee Table and Metavalues table Metavalues table is like master table.
In Application i will get multiple select box selection (DrivingLicense,Passport etc;) so that data will be inserted in comma(',') separated values So in my desired output i need to show as i need to show split those comma separated and for every MetaTypeID MetaTypeName as a row as showed in desired output
Metavalues table :
MetaID Metavaluedescription
1 Driving License
2 Passport
3 AadharCard
4 EducationalProof
5 ResidentialProof
CustomerMeta Table :
CustID MetaTypeID MetaTypeName
2 1,2,3,4,5 DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof
3 1,2,3DrivingLicense,Passport,AadharCard
Employee Table
EmpID CustID EmPname
1001 2Mohan
1002 3 ramu
Desired OutPut :
EMPID CustID EmPname MetaTypeID MetaTypeName
1001 2 Mohan 1 Driving License
1001 2 Mohan 2 Passport
1001 2 Mohan 3 AadharCard
1001 2 Mohan 4 EducationalProof
1001 2 Mohan 5 ResidentialProof
1002 3 ramu 1 Driving License
1002 3 ramu 2 Passport
1002 3 ramu 3 AadharCard
View 20 Replies
View Related
Sep 14, 2015
I have values in two columns separated by commas, like shown below:
I need the Output Like
How to do this in SQL server ?
View 6 Replies
View Related
May 3, 2015
I have a table Sample with data stored like below
ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL
I need the output like..
-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------
How can I do the same with a select query in SQL Server?
View 4 Replies
View Related
Jun 2, 2015
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
)
View 5 Replies
View Related
Mar 14, 2014
I have data in the below format .
NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512
I want the result in the below format
NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120
I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.
View 2 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
Jul 2, 2015
Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012 ....
View 5 Replies
View Related
Jul 2, 2015
i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.
This is the image path :
[URL]
View 3 Replies
View Related
Apr 10, 2006
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes.
But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half.
Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...)
I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified.
Any ideas?
Here is sample fie content to see what I mean:
342123, Jason, 12345
21, Kim,4567
32.43, John Paul, 1245
23, "Mr. T", 98764
12, "Peter, Paul, Mary", 09643
The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last.
(Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
View 3 Replies
View Related
Aug 27, 2015
I have a parameter value as shown below and this is dynamic and can growÂ
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA Â --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL Â --- each state to be followed with comma and the next state
correct query that will fetch  only state names and not the numbers.
View 8 Replies
View Related
Feb 5, 2004
Suppose I have a table like this
code Value
1 a
1 a
1 b
2 c
2 c
1 d
2 g
Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d
My output should be like this
1 a,b,d
2 c,g
Can anybody help me I can I do this with the help of a cursor or any other way?
Subhasish
View 1 Replies
View Related
Jun 26, 2007
I’m passing a comma separated parameter (home, world, child) into stored procedure.
I have a Slitter function which is basically creates a table out of delimited list.
My stored procedure needs to find matched records in one of the table based on delimited list.
I have something like this:
SELECT *
FROM Word
WHERE WordName IN (SELECT * FROM dbo.fxSplitter('home,world,child, ',')
I would like to have my stored procedure be able to select rows, even if comma delimited parameter holds part of the name like this “hom, wor, chil� .
Another words it will be SELECT * FROM Word WHERE WordName LIKE '%hom%' OR WordName LIKE '%wor%' OR WordName LIKE '%chil%'
View 3 Replies
View Related
Jan 27, 2008
I have a checkbox list on datalist as one column. when user selects more than one checkbox and click on apply. i concatenate IDs of checkboxes as '1,2'3' for e.g. and sending that to Stroe Procedure as varchar datatype parametrer. In Procedure i wanna update status of all three selected and i am using statement "update tbl set status=1 where pageid in('1,2,3'). It is saying it cannot convert varchar to int.
How can i do this task?
Thanks in advance.
View 2 Replies
View Related
Aug 20, 2004
Hi,
****SQL Server related question.
I have a table in which one of the columns (col1) holds a string, like: 1,2,3,4,5,6,7,8,9,10
I am passing an int value (@intValue) to the sproc.
What I want to be able to do is query the table like....
SELECT * FROM myTable where @intValue .... is in col1
Any ideas?
Thanks a lot!!!!
View 4 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
Oct 15, 2013
I'm trying to see the following comma separated sql statement using 'print' but it is throwing error "incorrect syntax near ','".
declare @csv varchar(max)
set @csv = '535,232'
print ''Select *
from tbl
where ',' + @csv + ',' like '%,' + mainid + ',%'''
View 10 Replies
View Related
Jun 6, 2014
What the difference between the following two codes in where clause?
The both provides the same resultset for me.
alter procedure
@fieldname varchar(50)
as
begin
select field1, field2 from table1
where ',' + @fieldname + ',' like '%,' + field3 + ',%'
end
The second code :
alter procedure
@fieldname varchar(50)
as
begin
select field1, field2 from table1
where @fieldname like '%' + field3 + '%'
end
View 2 Replies
View Related
Apr 10, 2015
I have Oracle query which seperates a text with commas to column data. Can we achieve this in SQL Server?
with t as (select 'abcd,123,defoifcd,87765,aoiwerwe' as str from dual)
select level as n, regexp_substr(str,'[^,]+',1,level) as val
from t
connect by regexp_substr(str,'[^,]+',1,level) is not null;
N VAL
1abcd
2123
3defoifcd
487765
5aoiwerwe
I'm working on SQL Server 2012, Windows 7.
View 1 Replies
View Related
Feb 26, 2008
Hello. I need to write a UDF that would split a comma separated list and return 4 values. I need to return the first 4 values and ignore the commas after that. If there are no commas in the string that's passed then just return the table with empty strings. The UDF should accept 2 inputs. The ntext and a position and return a value based on the position.For example: 1,2,3,textshould createPosition | Value-------------------------1|12|23|34|textand return a value based on the position. If there are more than 3 commas for example1,2,3,This string, though short, contains a commashould createPosition | Value-------------------------1|12|23|34|This string, though short, contains a commaand return a value based on the position. And if there are are less than 3 commas in the string passedFor example: 1,2 or NULL or 2:3.5 or This is a string with no commasshould createPosition | Value
-------------------------
1| (empty string)
2| (empty string)
3| (empty string)
4| (empty string)and return a value based on the position.This is what I wrote so far. CREATE function GetValueFromPosition (@Input nvarchar(4000), @position int)Returns nvarchar(4000)AsBegin -- Declare the return Variable Declare @ReturnValue nvarchar(4000) Select @ReturnValue = LTRIM(RTRIM(member_id)) From dbo.SplitString(@Input, ',') Where position = @position Return @ReturnValueEnd CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')-- This function splits a string of CSV values and creates a table variable with the values.-- Returns the table variable that it createsRETURNS @Strings TABLE( position int IDENTITY PRIMARY KEY, member_id varchar(8000))ASBEGIN Declare @index int Set @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURNEND I am trying to modify these according to what I need but its not working. Please help. Thank you.
View 1 Replies
View Related
May 3, 2005
I have a table called evidence, which has the following Fields
| evidence_id | Description| Standards|
E001 blagh 1.1,1.2,1.3
Ok I am trying to search the comma-separated string in the standards field using the like clause so I can display the evidence_id.
SQL looks like
SELECT Evidence.Standards, *
FROM Evidence
WHERE (((Evidence.Standards) Like '%1.1%'));
However it will not search through the list and select for example if I change 1.1 to 1.2. The commas wont allow it.
It works if I just have one item in the list that is just 1.1. Can anyone help me to search a comma-separated string for a certain string?
Thanks
Asylum
View 3 Replies
View Related
Jul 6, 2015
I have some column values:-
employee_salary | dept
30000 1
35000 1
40000 1
I need employee-salary in one row separated by comma by executing a sql query i.e
dept1_salary
30000, 40000, 50000
View 1 Replies
View Related
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
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
Jun 3, 2014
I just learned to use following query to convert columns to rows separated by comma.
Here is that :
Declare @list varchar(max)
select @list = isnull(@field+ ',','') + columname from table1
select @list
This produces the output I want but I'm confused with the statement. I just learnt it by heart. I don't know the meaning of it particular for the statement "select @list = isnull.............. from table1" . What exactly it does to give the desired output?
View 2 Replies
View Related
Jun 28, 2006
How to use comma separated value list in the where clause?
I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).
update tbl_Voters
set voted = true
where EmpID in @empIdsCsv
Where, @empIdsCsv = €™12,23,345,€™ (IDs of the employees)
Since the above is not possible I have done the following dynamic query:
-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}€¦
set @empIdsCsv = 'EmpID=' + substring(@empIdsCsv , 0, len(@empIdsCsv )) -- Remove trailing comma
set @empIdsCsv = replace(@empIdsCsv , ',', ' or EmpID=')
declare @markVoters varchar(8000)
set @markVoters = '
update tbl_Voters
set voted = true
where €™ + @empIdsCsv
--Execute the dinamic query
exec (@markVoters)
The above code generates the following dynamic query:
update tbl_Voters
set voted = true
where
EmpID= 12 or EmpID=23 or EmpID=345
The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).
Can someone suggest a better solution with the ability to use comma seperated values in the where clause?
View 3 Replies
View Related
Feb 24, 2007
I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!
Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?
If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.
Thanks in advance for any replies, they would be GREATLY appreciated :)
View 1 Replies
View Related
May 27, 2005
Hello,
I was wondering if it's possible to pass in a comma separated string
"12,14,16,18" and use it in a stored procedure with "IN" like this:
@SubRegions varchar(255) <-- my comma separated string
SELECT *
FROM myTable
WHERE tbl_myTable.SubRegionID IN (@SubRegions)
It tells me it has trouble converting "'12,14,16,18'" to an INT. :(
View 2 Replies
View Related
Feb 9, 2006
hi,
my sample SQL Server DB Tables are like,
SID Skill
--- -------
1 JAVA
2 ORACLE
3 C
4 C++
PID Skillset
--- ---------
1 1,2,3
2 2,4
3 1,2,3,4
4 3
I need the Query to display Person skills as follows...
PID Skillset
--- --------------
1 Java,Oracle,C
2 Oracle,C++
3 Java,Oracle,C,C++
4 C
and another query for Search..
if i give the search string as Java,C or i will pass the SID 1,3. i need to diplay the person records which contains the SID.
output will be...
PID Skillset
--- --------------
1 Java,Oracle,C
3 Java,Oracle,C,C++
4 C
or
PID Skillset
--- ---------
1 1,2,3
3 1,2,3,4
4 3
Plz help meee..
Thanking you in advance for your help.
View 1 Replies
View Related
Jan 5, 2015
I'd like to limit my query results to only items that match any part of a dynamic csv string table but am having some trouble (postgres SQL). Details: I need to calculate how many hours our staff spends seeing clients. Each staff has different appointments that can count toward this. The specified appointments for each staff are listed as comma separated values. My existing query calculates the appointment hours for each staff in a given time period.
However, I need limiting my query to only include specified activities for each staff. My current where clause uses IN to compare the appointment (i.e. activity) listed in the staff's schedule with what is listed an an approved appointment type (i.e. performance target activity). The query runs but it seems to only count one of the activities listed in the csv rather then count all the activities that match with the csv.
select (sum (kept)/60) from (select distinct rpt_scheduled_activities.staff_id as sid,
rpt_scheduled_activities.service_date, rpt_scheduled_activities.client_id,
from rpt_scheduled_activities inner join rpt_staff_performance_target on rpt_scheduled_activities.staff_id = rpt_staff_performance_target.staff_id where
[code]...
View 1 Replies
View Related
