18

As we can write in SQL to return single static value

select "Jan" as StartMonth

In the similar way, SQL should allow to write to return multiple values in same column

select {"Jan", "Feb", "Mar"} as Qtr1, {"Apr", "May", "Jun"} as Qtr2

When we need data as table to perform join and data is unknown at Sp level, i.e., it is coming from parameter, it appears unnecessary tasks to create a temp table and insert split values in the temp table. Correct me if such feature exist in MS SQL server. Or better way to have it other than pivot and temp table.

hungryMind
  • 6,931
  • 4
  • 29
  • 45
  • How do you plan to use this? Can you provide an example of what you are trying to achieve using the static values? – shahkalpesh Jun 03 '11 at 10:50
  • Are you looking to Concatenate the results frm a single column (ie: Multiple Rows) into a single value (ie: Scalar) like a comma seperated string ? Also, if you are hard coding these as per example above, whats wrong with : select 'Jan, Feb, Mar' as Qtr1, 'Apr, May, Jun' as Qtr2 ? – Dave Long Jun 03 '11 at 10:50
  • 1
    @shah; Its a generic question, I know there are alternatives but it would have nice if we can write this way. Common scenario is we have lots of SP which takes years as comma separated string for reports and we have split and store them in temp table or use table variable. – hungryMind Jun 03 '11 at 11:00

3 Answers3

39

Will this give you what you want?

SELECT 'Jan' AS Qtr1, 'Apr' AS Qtr2
UNION ALL SELECT 'Feb' AS Qtr1, 'May' AS Qtr2
UNION ALL SELECT 'Mar' AS Qtr1, 'Jun' AS Qtr2
Tim Rogers
  • 21,297
  • 6
  • 52
  • 68
  • 5
    That looks cool. You can even make it a bit shorter: SELECT 'Jan' AS Qtr1, 'Apr' AS Qtr2 UNION SELECT 'Feb', 'May' UNION SELECT 'Mar', 'Jun' – Paul Dec 23 '14 at 10:32
  • I sometimes use this in CTE's (WITH parms AS (SELECT ...)) in views and scripts to sort of parameterize when I'd rather not (or can't) declare variables etc or when I need a special set of static data to work with. It's nice because I can keep it near the top of the script. I do wish there was a shorter way to write it such as hungryMind's suggestion, but it's still handy! – kwill May 22 '15 at 13:04
  • And how to `JOIN` that construct as a sub-select onto another table, for instance ... ? – NeilG Apr 27 '22 at 09:23
8

I know this question is tagged , but it was amongst the top google results when I was looking for the Postgres answer, so here it is for Postgres:

This:

VALUES ('Jan', 'Apr'), ('Feb', 'May'), ('Mar', 'Jun');

Is equivalent to this:

SELECT 'Jan' AS column1, 'Apr' AS column2
UNION ALL SELECT 'Feb', 'May'
UNION ALL SELECT 'Mar', 'Jun'
Wildcard
  • 1,302
  • 2
  • 20
  • 42
  • I have tried this and it works. Is there a to do this where you specify the column name? thanks. where I specify the name of the column in the Values Command. – Artanis Zeratul Aug 04 '20 at 21:56
  • @ArtanisZeratul no, that's not possible to my knowledge; see the documentation I linked. You can use "select" if you need column names. (Or "select" from "values".) – Wildcard Aug 04 '20 at 22:03
  • Yeah, I ended using the equivalent you posted here. – Artanis Zeratul Aug 05 '20 at 01:42
0

As of SQL Server 2008 you can use Table Valued Parameters - whereby you can pass in a TABLE of data to then use/join on as you wish inside the sproc instead of having to pass in a CSV string that you would need to split.

Check out this MSDN article:

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200