Convert Comma Separated column value to rows

by

try this

 SELECT A.[id],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

refer here

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

More Related Contents:

  1. How do I escape a single quote in SQL Server?
  2. Subquery using Exists 1 or Exists *
  3. How can I query a value in SQL Server XML column
  4. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  5. SQL – How to store and navigate hierarchies?
  6. SQL Server indexes – ascending or descending, what difference does it make?
  7. Update records in table from CTE
  8. How to capitalize the first letter of each word in a string in SQL Server
  9. INT vs Unique-Identifier for ID field in database
  10. SQL Server date format function
  11. SQL: Select dynamic column name based on variable
  12. Composite Primary key vs additional “ID” column?
  13. Can a sql server table have two identity columns?
  14. The SQL OVER() clause – when and why is it useful?
  15. Saving changes after table edit in SQL Server Management Studio
  16. Eliminating duplicate values based on only one column of the table
  17. Logging ALL Queries on a SQL Server 2008 Express Database?
  18. SQL query to find Nth highest salary from a salary table
  19. How to get export output in “real” CSV format in SQL Server Management Studio?
  20. Merge overlapping date intervals
  21. Script to save varbinary data to disk
  22. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  23. alternatives to REPLACE on a text or ntext datatype
  24. SQL poor stored procedure execution plan performance – parameter sniffing
  25. How to find out what is locking my tables?
  26. SQL – How do I get only the numbers after the decimal?
  27. How to retrieve records for last 30 minutes in MS SQL?
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. Why do SQL Server Scalar-valued functions get slower?
  30. Executing SQL query on multiple databases

Leave a Comment