Replace comma separated master data with description in a column
how to create comma separated values in excel
how to split a comma-separated value to columns in sql server
convert row to comma separated list
excel: split comma separated values into rows
tableau create comma separated list
how to split a comma-separated values to columns in sql server
how to split comma separated values in sql query
There are 2 SQL Server tables:
Name Status Code ------------------------------------ Product 1 1001, 1003 Product 2 1001, 1005, 1006
Code Description ------------------------------------ 1001 State A 1003 State B 1005 State C 1006 State D ...
I wish to get something like:
Product Status --------------------------------------------- Product 1 State A, State B Product 2 State A, State C, State D
You can use a query like below:
create table Products (Name varchar(100), [Status Code] varchar(100)); insert into Products values ('Product 1', '1001, 1003') ,('Product 2', '1001, 1005, 1006'); create table [Status] (Code varchar(100), Description varchar(100)); insert into [Status] values ('1001', 'State A') ,('1003', 'State B') ,('1005', 'State C') ,('1006', 'State D') ; WITH X AS ( SELECT P1.Name, S.Description FROM ( SELECT *, cast('<X>'+replace(P.[Status Code],',','</X><X>')+'</X>' as XML) AS xmlprods FROM Products P )P1 CROSS APPLY ( SELECT fdata.D.value('.','varchar(100)') AS splitdata FROM P1.xmlprods.nodes('X') AS fdata(D)) O LEFT JOIN [Status] S ON S.Code= LTRIM(RTRIM(O.splitdata )) ) SELECT Name, Description= STUFF(( SELECT ',' + Description FROM x AS x2 WHERE x2.Name = x.Name ORDER BY Name FOR XML PATH, TYPE).value(N'.',N'varchar(max)'), 1, 1, '') FROM X GROUP BY Name
How to replace comma separated values to comma separated ids in , I used a combination of Data>Text to Columns , VLOOKUP and Concatenate Here your Ingredients are in Column D and their respective IDs in Column E (i.e. your Master table in Sheet 2). enter image description here. You should NOT store several values comma-separated into a single column. This is (a) a violation of the first normal-form of database design, and (b) a horribly bad design choice since it makes it impossible to create database-level foreign key constraints that ensure data integrity – marc_s Jun 3 '18 at 7:22
You can try this. I used a temporary table but you can use any means you want
SELECT p.Product , p.Status , s.Description INTO #tmp FROM ( SELECT Name Product, TRIM(value ) Status FROM tblProduct CROSS APPLY STRING_SPLIT(TRIM(StatusCode), ',') ) p JOIN tblStatus s ON s.Code = p.Status
select distinct Product , stuff(( select ',' + tmp2.Description from #tmp tmp2 where tmp2.Product = tmp1.Product order by tmp2.Product for xml path('') ) ,1,1,'') as StatusCode from #tmp tmp1 group by Product
Comma Separated value within a Column |Tableau Community , Solutions Overview · Industries · Departments · Capabilities · Technology I am trying to figure out if there is a way to use this data in Tableau without (I assume its a string data type)-- REPLACE([FieldNameHere], ",", "") does this So there are always 1,440 comma separated values in my activity stream There has been a discussion of various methods of doing this, including some informal performance testing I've done, on SO: oracle SQL — Replace comma separated string with another set of values - Stack Overflow The solution above wasn't the solution marked "best answer" there,
Below Query will help to make any comma separated to list.
SELECT A.OtherID, Split.a.value('.', 'VARCHAR(100)') AS Data FROM ( SELECT OtherID, CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data FROM Table1 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
How can a comma separated list be converted into cells in a column , Highlight the column that contains your list. Go to Data > Text to Columns. Choose Delimited. Click Next. Choose Comma. Click Next. Use this tool to convert a column into a Comma Separated List. Copy your column of text in Excel. Paste the column here (into the leftmost textbox) Copy your comma separated list from the rightmost textbox. Paste your comma separated list wherever you wish.
How to split a comma-separated value to columns in sql server, I want to separate the comma delimited string into two columns type='p' and number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test) Reference to database and/or server name in 'master..spt_values' is not 1) Remove the commas, leaving a bunch of numbers (I assume its a string data type)-- REPLACE([FieldNameHere], ",", "") does this 2) Count the number of values in the string, and use that to analyze the count of minutes-- LENGTH([FieldNameHere]) returns this I am referencing these calcs from memory,
Change CSV Delimiter, Csv column delimiter changer. World's simplest csv tool. Free online CSV column separator changer. Just load your CSV, enter the new delimiter, and columns will A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
MySQL: For each group, retrieve a comma-separated list of values in , This will cause horrible, data-destroying bugs in production. You can use it to retrieve a comma-separated list of all values in a given column within each group. For each user_id we would like a comma-separated list of post IDs. PostgreSQL: How to add/remove/modify array values (and how to replace 1 value with select description,(column_value).getnumberval() CREATE OR REPLACE TYPE lsc_number AS OBJECT This time I found an article about how to select on a comma
- Terrible data model.
- The root of your problem is that you have delimited data in your tables. This is NOT how you should be handling data. It should be one row for each status code. Now you are struggling to retrieve data because the design is not normalized.
- There are plenty of examples on how to parse delimited lists, and how to create them using SQL Server. On this site alone there are probably 1000's of answers and questions, many of the latter are probably marked as duplicates. What have you tried so far? What version of SQL Server are you using? The data model is wrong though, as Sean has stated. Ideally fix that issue, and the problem solves itself.
- I know that the design is not as expected, but I can't change it.
- Learn some database design. Your current structure will give you nightmare later on. Even now, things that are supposed to be a simple join become complicated because of the comma delimited column.
- Thank you very much, this is answer. Works nice.