How do I expand comma separated values into separate rows using SQL Server 2005?

by

Take a look at this function. I’ve done similar tricks to split and transpose data in Oracle. Loop over the data inserting the decoded values into a temp table. The convent thing is that MS will let you do this on the fly, while Oracle requires an explicit temp table.

MS SQL Split Function
Better Split Function

Edit by author:
This worked great. Final code looked like this (after creating the split function):

select pv.productid, colortable.items as color
from product p 
    cross apply split(p.color, ',') as colortable

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. SQL update query using joins
  4. How to update two tables in one statement in SQL Server 2005?
  5. Referring to a Column Alias in a WHERE Clause
  6. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  7. SQL query to select dates between two dates
  8. Create a date from day month and year with T-SQL
  9. Selecting COUNT(*) with DISTINCT
  10. Querying data by joining two tables in two database on different servers
  11. Are there any disadvantages to always using nvarchar(MAX)?
  12. SQL – Subtracting a depleting value from rows
  13. Turn off constraints temporarily (MS SQL)
  14. Get the week start date and week end date from week number
  15. Pass table as parameter into sql server UDF
  16. conditional unique constraint
  17. What’s the best way to select the minimum value from several columns?
  18. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  19. SELECT FOR UPDATE with SQL Server
  20. Performance of SQL Server 2005 Query
  21. OPENXML with xmlns:dt
  22. How to check which locks are held on a table
  23. How to select date without time in SQL
  24. How to find sum of multiple columns in a table in SQL Server 2005?
  25. Query times out from web app but runs fine from management studio
  26. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  27. SQL Sort Order with Null values last
  28. Why do SQL Server Scalar-valued functions get slower?
  29. Insert all values of a table into another table in SQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment