Concatenate results from mutlple sql queries

In order to get your queries into one result set, the values in each column must be the same data type.  So, the result from Query 1 must be the same data type as the result from Query 2.

You can cast your columns to the same data type, but you should know the max size possible in order to avoid errors.

Example:

DECLARE @Batch_Num VARCHAR(1) = 1;
SELECT  CAST('<V1.0>,"' + SUBSTRING(@Batch_Num, 7, 2) + SUBSTRING(@Batch_Num,
                                                              10, 2) + 'C"' AS VARCHAR(200)) AS MyResult
UNION ALL
SELECT  CAST('*,"' + SUBSTRING(@Batch_Num, 2, 1) + SUBSTRING(@Batch_Num, 4, 2)
        + SUBSTRING(@Batch_Num, 7, 2) + SUBSTRING(@Batch_Num, 10, 2)
        + 'c",,"PONUM",,,"' + +SUBSTRING(@Batch_Num, 2, 1)
        + SUBSTRING(@Batch_Num, 4, 2) + SUBSTRING(@Batch_Num, 7, 2)
        + SUBSTRING(@Batch_Num, 10, 2) + 'c.dat"' AS VARCHAR(200)) AS MyResult;

Select allOpen in new window

In this example, I arbitrarily chose 200 as the max size for the result.  You will need to choose an appropriate size, which largely depends on the max size of @Batch_Num since the other characters in your query are static and won't change size.

Please also note that I'm using UNION ALL instead of UNION.

You can use a UNION statement between the two queries and have it return the results as two rows:

Select '<V1.0>,"' + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'C"'
UNION
Select '*,"' + substring(@Batch_Num, 2, 1) + substring(@Batch_Num, 4, 2) + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'c",,"PONUM",,,"' +
                 + substring(@Batch_Num, 2, 1) + substring(@Batch_Num, 4, 2) + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'c.dat"'

Select allOpen in new window

Would that work?

@Ryan -  UNION ALL is likely a better option in this case, no?

UNION ALL is 100% definitely, absolutely, utterly and completely the better method here

When you use UNION by itself you are actually doing UNION DISTINCT (the DISTINCT is "assumed")

so IF 2 rows were identical UNION DISTINCT would only allow one row as a result
but if 2 rows were identical UNION ALL would present all the rows

(thinks: enough. stop now.)

No Points please

UNION ALL  works well for the first two queries:
declare @Batch_Num as varchar(20)

set @Batch_Num = '15-11-23-01'

Select '<V1.0>,"' + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'C"'
UNION ALL
Select '*,"' + substring(@Batch_Num, 2, 1) + substring(@Batch_Num, 4, 2) + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'c",,"PONUM",,,"' +
                + substring(@Batch_Num, 2, 1) + substring(@Batch_Num, 4, 2) + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'c.dat"'

When presenting the results to text, it gives me two lines, just as I want.  The third query, however creates a problem.  Here is the query:

Select Cast( (ROW_NUMBER() Over ( order by GlassID, GlassHeight)) as varchar( 20 )) + ',1,0,"' + Rtrim(GlassID) + '",' +
           Rtrim(GlassWidth) + ',' + Rtrim(GlassHeight) + ',,"SPACER",,,,,,,,,"LC"'
from pct_Glass_Cuts
where batch_number = @Batch_Num
Order by GlassID, GlassHeight

When running it as a separate query, it works fine.  The first few lines of the result set look like this:

1,1,0,"GL5/32CLR",,,,"SPACER",,,,,,,,,"LC"
2,1,0,"GL5/32CLR",20.500,41.625,,"SPACER",,,,,,,,,"LC"
3,1,0,"GL5/32CLR",20.500,41.625,,"SPACER",,,,,,,,,"LC"
4,1,0,"GL7/32CLR",,,,"SPACER",,,,,,,,,"LC"
5,1,0,"GLCL.1/4",36.375,18.625,,"SPACER",,,,,,,,,"LC"
6,1,0,"GLCL.1/4",36.375,18.625,,"SPACER",,,,,,,,,"LC"
7,1,0,"GLCL.1/4",39.00,18.750,,"SPACER",,,,,,,,,"LC"
8,1,0,"GLCL.1/4",39.00,18.750,,"SPACER",,,,,,,,,"LC"
9,1,0,"GLCL.1/4",9.5625,43.6875,,"SPACER",,,,,,,,,"LC"
10,1,0,"GLCL.1/4",9.5625,43.6875,,"SPACER",,,,,,,,,"LC"
11,1,0,"GLCL.1/4",9.5625,43.6875,,"SPACER",,,,,,,,,"LC"
12,1,0,"GLCL.1/4",9.5625,43.6875,,"SPACER",,,,,,,,,"LC"

However, when I use UNION ALL with this query I get these errors:

Msg 207, Level 16, State 1, Line 15
Invalid column name 'GlassID'.
Msg 104, Level 16, State 1, Line 15
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'GlassHeight'.
Msg 104, Level 16, State 1, Line 15
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Would it help if I try to figure out how to use a CTE for the third query, or is there a better way to get the result I need?

T

Thank you Zephyr-Hex.  One additional issue.  When I run this as text, I get a dashed line at the top, like this:
-----------------------------------
Is there a way to get rid of that?

Change the delimiter type in Tools->Options->Query Results->SQL Server->Results To Text.  I think the default is Column Aligned, which produces the dashes.  Use one of the other options, or try the Custom Delimiter.  I use Custom Delimiter with pipe ( | ) and do not get the dashes.

Thank you zephyr_hex.  The setting that actually did it was to turn off "Include column headers in the result set."

Thank you zephyr_hex.  You have been very helpful.  I now need to figure out how to set Results to Text, Results to File and Column Headers off programmatically, so I can run the query in a stored procedure.  If I don't figure it out, I will post another question called Programmatically Setting Query Options.  Watch for it.  Thanks again.

T

You likely will want to create a stored procedure that returns the result set to your calling program.  "Results To Text", etc are options in SSMS -- an environment in which a user can issue queries against a SQL database.  You wouldn't use SSMS to run a query from code.