How to transpose CSV string into rows?

Question

How to transpose CSV string in rows? In the example below, rows are transposed into CSV string but I would like to do the inverse operation (split this string "Day,Evening,Night" into rows)?. How to do that?

USE AdventureWorks
GO
-- Check Table Column
SELECT [Name]
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT STUFF(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
GO

The example is taken from here.

I edited my question to make it more clear

jrara
– jrara

2011-04-17 06:44:19 +00:00
Commented Apr 17, 2011 at 6:44 — jrara
– jrara, Commented Apr 17, 2011 at 6:44

gbn · Accepted Answer · 2011-04-17 08:35:04Z

1

There are several techniques in the canonical reference here: Arrays and Lists in SQL Server 2005 and Beyond by Erland Sommarskog

We use the numbers table technique

answered Apr 17, 2011 at 8:35

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AlexS · Accepted Answer · 2011-04-17 08:54:52Z

1

I would recommend using UDF written in C# - relatively easy to develop (if you're familiar with C#) and the fastest choice when it comes to execution.

Reading a reference provided by gbn will give much deeper understanding of the subject, though will certainly take longer.

answered Apr 17, 2011 at 8:54

AlexS

2,38615 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to transpose CSV string into rows?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related