20

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.

Improve this question
3
  • How do you plan to use this? Can you provide an example of what you are trying to achieve using the static values?
    – shahkalpesh
    Commented Jun 3, 2011 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
    Commented Jun 3, 2011 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
    Commented Jun 3, 2011 at 11:00

3 Answers 3

Reset to default
43

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
Improve this answer
3
  • 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
    Commented Dec 23, 2014 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
    Commented May 22, 2015 at 13:04
  • And how to JOIN that construct as a sub-select onto another table, for instance ... ?
    – NeilG
    Commented Apr 27, 2022 at 9: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'
Improve this answer
3
  • 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
    Commented Aug 4, 2020 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
    Commented Aug 4, 2020 at 22:03
  • Yeah, I ended using the equivalent you posted here.
    – Artanis Zeratul
    Commented Aug 5, 2020 at 1: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:

Improve this answer
2
  • Yes but thats 2008, my client won't buy it. Its not a requirement but feature nice to have.
    – hungryMind
    Commented Jun 3, 2011 at 11:47
  • @hungryMind - Ok, thought it worth a mention as you didn't state which version you were using
    – AdaTheDev
    Commented Jun 3, 2011 at 12:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.