SELECT a string formatted array by row in SQL Server

Question

For instance I have a string parameter @p_colors with a value like:

'yellow','blue','red'

which is passed into a stored procedure. And when I try to select it like this:

EXECUTE(CONCAT('SELECT ', @p_colors));

The result is the values are selected by columns.

No column name | No column name | No column name
------------------------------------------------
Yellow         | Blue           | Red

How will I do it if I want to display them by row like this:

No column name |
----------------
Yellow         |
----------------
Blue           |
----------------
Red            |
----------------

using only the string formatted parameter. Is there any way in SQL to make this?

EDIT:

The actual result will be inserted into a temp table so the actual code is :

EXECUTE('INSERT INTO #colors(color) SELECT ', @p_colors);

Downvote reason: not even minimal reaserch. a simple search for "split strings sql server" would give you 478,000 on google, and 841,000 on Bing. — Zohar Peled, Apr 29 '17 at 07:35
@Neer how is it different? It's a simple string split question. — Zohar Peled, Apr 29 '17 at 07:35
@NEER I'm not following. Why is splitting a hard coded string should be different then splitting a string inside a parameter? It's the same thing — Zohar Peled, Apr 29 '17 at 07:38
That I understand, what I don't understand is **why**. You can vote to reopen, but if you can provide a good reason why it's different then any other string splitting scenario I'll be happy to revoke my close vote. — Zohar Peled, Apr 29 '17 at 07:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142955/discussion-between-neer-and-zohar-peled). — neer, Apr 29 '17 at 08:01

neer · Accepted Answer · 2017-04-29T08:01:03.347

1

You can as the below:

SELECT * FROM (VALUES('yellow'), ('blue'), ('red')) t(c)

Update:

DECLARE @Param NVARCHAR(MAX) = '''yellow'', ''red'', ''blue'''
DECLARE @Query NVARCHAR(MAX) 
SELECT @Query = 'SELECT * FROM (VALUES(' + REPLACE(@Param, ''',', '''),(') + ')) t(c)'
--SELECT @Query
EXEC sp_executesql  @Query

edited Apr 29 '17 at 08:01

answered Apr 29 '17 at 07:33

neer

4,031
6
20
34

In this solution I think I have to make my string to be formatted like this `('yellow'),('blue'),('red')`. How can I make it using only `'yellow', 'red', 'blue'` – Red Magda Apr 29 '17 at 07:37
@RedMagda Answer updated. – neer Apr 29 '17 at 08:01
2

The updated solution works well for me, very simple yet effective – Red Magda Apr 29 '17 at 08:22

SELECT a string formatted array by row in SQL Server

1 Answers1