SQL Server 2012 :: Comma Separated In A Single Result
May 30, 2014
I have the following table
CREATE Table #Table1
(
ID INT, Name VARCHAR(50), Class VARCHAR(10)
)
INSERT INTO #Table1
Select 1, 'name1', 'a' UNION ALL
Select 2, 'name1', 'a' UNION ALL
[Code] ....
Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?
CREATE Table #Table2
(
ID INT, CommaSeparated VARCHAR(100)
)
INSERT INTO #Table2
Select 1, 'name1, a' UNION ALL
[Code] ...
What I have
Select * FROM #Table1
Final Result
Select * FROM #Table2
Note that I still want to see all the IDs regardless.
If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.
CREATE Table #Table3
(
CommaSeparated VARCHAR(100)
)
INSERT INTO #Table3
Select 'name1, a' UNION ALL
Select 'name2, b, c, d' UNION ALL
Select 'name3, e, f'
Select * FROM #Table3
Hi,I'n in an environment where I cannot make stored procedures. Now I needto make a query with a subquery in the SELECT part which gives a commaseparated list of results:SELECTp.id,listFunction(SELECT name FROM names WHERE name_parent=p.id) AS'nameList'FROM projects AS pThis query should return something like:1, "john,mike,petra"2, "bob,carl,sandra,peter,etclistFunction is (of course) not (yet) defined. Is this possible withoutthe use of stored procedures?Mike
I have an input parameter of an SP which value will be passed with different combinations with 2 seperators (comma and pipe)
Value to the parameter is like this :Â Â Â Â Â '10|22|microsoft,20|25|sql,30|27|server,40|29|product'
I want output like this
Column1Â Â Â Â Â Â Column2Â Â Â Â Â Column3 10Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 22Â Â Â Â Â Â Â Â Â Â Â Â Â microsoft 20Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 25Â Â Â Â Â Â Â Â Â Â Â Â Â sql 30Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 27Â Â Â Â Â Â Â Â Â Â Â Â Â server 40Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 29Â Â Â Â Â Â Â Â Â Â Â Â Â product
Pipe separator is for column and comma separator is for row.
I know if its a single separator, it can be done with function but how to do if its 2 separators?
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.
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.
Hi, I have a table called geofence. It has a primary key geofence_id. Each geofence consists of a set of latitudes and latitudes. So I defined two columns latitude and longitude and their type is varchar. I want to store all latitude/longitude values as a comma separated values in latitude/longitude columns So in general how do people implement these types of requirements in relational databases?
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 have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma separated parameter and then I have a function which converts this parameter to a table with separate rows.
E.g. : a,b,c,d
Converted to result table
result a b c d
I want to insert each row of the result table into another table. How to do that.
E.g., the table after the function is :
CREATE TABLE #result ( Subject varchar(100) )
insert into #result values ('a') insert into #result values ('b') insert into #result values ('c') insert into #result values ('d')
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday') --- DECLARE @ITEM_ID uniqueidentifier DECLARE ITEM_CURSOR CURSOR
Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.
A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.
The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.
I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.
This needs to be repeated for each row
SELECT IP_List , LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1 , REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1 FROM IpExclusionTest
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 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.
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
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