How to transpose a comma delimited field

Transact-SQL
1

re: SQL Server 2012

I have a SQL table that has one column with comma delimited values in it (see example below)

I would like to be able to transpose that comma delimited column so i end up with something like this but i'm not sure how to accomplish in SQL

Any ideas much appreciated

NB I can do this quite easily outside of SQL (as i did in my example) but do not have that luxury with this particular DB unfortunately as it is managed offsite so hoping i can do it in a query?

2

Normal approach is to use a Splitter Function (written in SQL) which will transform each value in a delimited list into a row (the "row" normally has two columns, the Ordinal item number, and the value).

To apply that to multiple rows in your example, each with their own delimited-list-column, you can CROSS APPLY the Function to the Data.

A Mickey Mouse function may be good enough, but I suggest using something robust - then you can just put that in your Grab Bag for the future without having to worry about efficiency down the line :slight_smile:
I suggest Jeff Moden's splitter function

http://www.sqlservercentral.com/articles/Tally+Table/72993/

3

Thanks for reply. have been trying the following (without the firstname,lastname columns) but am getting the following error.

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'APPLY'.

SELECT A.[email],
Split.a.value('.', 'VARCHAR(255)') AS String
FROM (SELECT [email],
CAST ('' + REPLACE([indexterms], ',', '') + '' AS XML) AS String
FROM alertRecipients) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Taken from:

4

Splitting w/ XML requires that the string be converted to XML w/ XML tags... You skipped the part where the tags were added (they aren't optional).
The following should work for you...

SELECT A.[email],
Split.a.value('.', 'VARCHAR(255)') AS String
FROM (SELECT [email],
CAST ('<M>' + REPLACE([indexterms], ',', '</M><M>') + '</M>' AS XML) AS String 
FROM alertRecipients) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

That said, unless you're actually interested in learning XML in SQL Server, follow @Kristen's advise and use a tally table based splitter like Jeff Moden's DelimitedSplit8K. It will be a much better performer.

5

Thanks for reply

Actually i've got those in there - i've just done a quick test and when i paste the code into the SQL Team reply box they get removed - didn't notice before.

Never mind i'll have a read of that marathon article Kristen posted

6

It's actually a well written and extremely informative article, so I would encourage you to actually read the article in it's entirety.
That said, if you're new(ish) to SQL, I could see where it could be a little esoteric and long winded... In that case, simply scroll to the bottom of the article and copy/paste the function code.

7

I agree, that reduces the uptake of that function - just having the code easily available would help, and is all the newcomer needs.

If you need to understand the background there are really good articles out there which cover lots of other, related, stuff too so I don't think you need to read this one specifically - and when you do then perhaps there are others you should read too in order to have a deep understanding :slight_smile:

The best in dept explanation of all this stuff that I know if is Erland Sommarskog's site

1 Like