Concatenate results from mutlple sql queries

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
T HoecherlDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperCommented:
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 McCauleyEnterprise Analytics ManagerCommented:
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?
zephyr_hex (Megan)DeveloperCommented:
@Ryan -  UNION ALL is likely a better option in this case, no?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PortletPaulEE Topic AdvisorCommented:
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 HoecherlDeveloperAuthor 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
zephyr_hex (Megan)DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
T HoecherlDeveloperAuthor 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?
zephyr_hex (Megan)DeveloperCommented:
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 HoecherlDeveloperAuthor Commented:
Thank you zephyr_hex.  The setting that actually did it was to turn off "Include column headers in the result set."
T HoecherlDeveloperAuthor 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
zephyr_hex (Megan)DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.