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):
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
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):
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
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
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 )
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 ....
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.
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.
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.)
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.
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
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%'
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.
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;
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.
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
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?
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 :)
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.
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
