Concatenate results from mutlple sql queries

T Hoecherl
T Hoecherl used Ask the Experts™
on
I have three SQL queries, each of which gives me a result.  What I want is for the results to be concatenated and either inserted in a table that can then be exported to a text file or directly to a text file.  For example, the first two queries and their results are:

Query1:
Select '<V1.0>,"' + substring(@Batch_Num, 7, 2 ) + substring(@Batch_Num, 10, 2) + 'C"'

Result:
<V1.0>,"2301C"

Query2:
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"'

Result:
*,"5112301c",,"PONUM",,,"5112301c.dat"

I want to run the queries together and get this result:
<V1.0>,"2301C"
*,"5112301c",,"PONUM",,,"5112301c.dat"

How can I accomplish this?

T
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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;

Open 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.
Ryan McCauleySenior Data Architect

Commented:
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"'

Open in new window


Would that work?
Top Expert 2010

Commented:
@Ryan -  UNION ALL is likely a better option in this case, no?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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
T HoecherlDeveloper

Author

Commented:
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
Top Expert 2010
Commented:
You could insert the 3rd query into a temp table in order to get the ordering you need.  then just UNION ALL SELECT * FROM #tmp_table, like so:


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

Open in new window


THEN:

Query1
UNION ALL
Query2
UNION ALL
SELECT * FROM #tmp_table

Open in new window


Here is a SQL Fiddle demo.  Please note I've had to simulate using a temp table because the fiddle doesn't allow it.
T HoecherlDeveloper

Author

Commented:
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?
Top Expert 2010

Commented:
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.
T HoecherlDeveloper

Author

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

Author

Commented:
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
Top Expert 2010

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial