how to join dynamic SQL results with another query

[new DBA in '09]

Hi Everybody,

I'm using dynamic SQL to execute a pivot query for a sales report
since the column names will change by month. The very last statement
is "EXECUTE sp_executesql @query", where @query is my pivot query
string. How do I join these results with another query so as to get
one resulting dataset?

For example, here's my "other query" results:

SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName]
UNION ALL
SELECT 2 AS [CustId], 'Microsoft'

And here's my pivot query results:

SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales]
UNION ALL
SELECT 2, 14.79, 25.99

Ultimately, I want the following results:

SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName], 35.99 AS [Jan_Sales],
104.52 AS [Feb_Sales]
UNION ALL
SELECT 2, 'Microsoft', 14.79, 25.99

I suppose this has nothing to do with my pivot query. I could execute
any query using dynamic sql, but I'm wondering how to join the results
with another query.

Any advice you could give would be greatly appreciated. We don't have
SSAS set up yet and I'm not qualified to run it yet, anyway.

Thanks,
Eric

[Plamen Ratchev]

You could use OPENQUERY to execute the dynamic SQL and join the result
set. Good to read the following first:
http://www.sommarskog.se/share_data.html#OPENQUERY

Alternative is to use INSERT...EXEC to store the result set to table
and join the table.

--
Plamen Ratchev
http://www.SQLStudio.com

[sam]

SELECT Test1.*,Test2.Jan_Sales,Test2.Feb_Sales FROM
(SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName]
UNION ALL
SELECT 2 AS [CustId], 'Microsoft' ) AS Test1
LEFT OUTER JOIN
--And here's my pivot query results:
(SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales]
UNION ALL
SELECT 2, 14.79, 25.99 ) AS Test2
ON Test1.CustId = Test2.CustNum

[Uri Dimant]

WITH cte1

AS

(

SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName]

UNION ALL

SELECT 2 AS [CustId], 'Microsoft'

),cte2

AS

(

SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales]

UNION ALL

SELECT 2, 14.79, 25.99

)

SELECT * FROM cte1 JOIN cte2 ON

cte1.[CustId]=cte2.[CustNum]

"new DBA in '09" <ericb...@gmail.com> wrote in message
news:237d38c1-27c0-4ec7...@11g2000prv.googlegroups.com...

[Erland Sommarskog]

new DBA in '09 (ericb...@gmail.com) writes:
> I'm using dynamic SQL to execute a pivot query for a sales report
> since the column names will change by month. The very last statement
> is "EXECUTE sp_executesql @query", where @query is my pivot query
> string. How do I join these results with another query so as to get
> one resulting dataset?
>
> For example, here's my "other query" results:
>
> SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName]
> UNION ALL
> SELECT 2 AS [CustId], 'Microsoft'
>
> And here's my pivot query results:
>
> SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales]
> UNION ALL
> SELECT 2, 14.79, 25.99
>
> Ultimately, I want the following results:
>
> SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName], 35.99 AS [Jan_Sales],
> 104.52 AS [Feb_Sales]
> UNION ALL
> SELECT 2, 'Microsoft', 14.79, 25.99
>
> I suppose this has nothing to do with my pivot query. I could execute
> any query using dynamic sql, but I'm wondering how to join the results
> with another query.

That do would have to be a venture with dynamic SQL, since you don't
know the structure of the result set of the pivot query.

I think the best option is to include the join when building the
pivot query from the start.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[new DBA in '09]

Thank you all. It seems only Erland and Plamen understood my question
as intended because I don't see any hint of dynamic SQL included in
Uri's or Sam's suggestions.

Erland, I like your advice about including the join when building the
pivot query, but I had trouble when I tried that yesterday before
starting this thread. (Probably has something to do with me being a
PIVOT noob.) Could you please point me in the right direction on
how this can be done? I have a very simple dynamic SQL pivot query:

DECLARE @columns varchar(8000);
SELECT @columns = coalesce(@columns + ',[' + cast(WW AS varchar) +
']',
'[' + cast(WW AS varchar) + ']')
FROM dbo.PhsCounts
GROUP BY WW;

DECLARE @query nvarchar(4000)

SET @query = '
SELECT *
FROM (SELECT dbo.GetProviderCode(ProviderId) AS ProviderId, PhsCount,
WW FROM PhsCounts) AS x
PIVOT
(
sum(PhsCount)
FOR WW
IN (' + @columns + ')
)
AS p'

EXECUTE sp_executesql @query

Thank you for your help again.

[Erland Sommarskog]

new DBA in '09 (ericb...@gmail.com) writes:

> Erland, I like your advice about including the join when building the
> pivot query, but I had trouble when I tried that yesterday before
> starting this thread. (Probably has something to do with me being a
> PIVOT noob.) Could you please point me in the right direction on
> how this can be done? I have a very simple dynamic SQL pivot query:

I did not say it was easy. :-)

It is not clear to me how the other query relates to your data,
but try it this way. First write your query with all data you want,
but keeping the data you want to pivot on a rows. Once you have
this query, you can use that as a starting point to built the pivot
query.

Also look at http://www.sommarskog.se/dynamic_sql.html#Crosstab, and
particular the procedure pivot_sp mentioned in that section.

> SELECT @columns = coalesce(@columns + ',[' + cast(WW AS varchar) +
> ']',
> '[' + cast(WW AS varchar) + ']')
> FROM dbo.PhsCounts
> GROUP BY WW;

Be aware of that this construct relies on undefine behaviour. There
is no guarantee that this will produce the comma-separated list
you are looking for.

[new DBA in '09]

Thanks, Erland. I found your suggestion to join the data from my
"other" query to the pivot data source to be right on. It's
definitely not difficult--if one has had some practice with the PIVOT
statement--and doesn't require any linked servers or other unnecessary
complexities. I'm having the problem of some rows duplicating in the
final results, but I imagine that's just a problem within my query
that needs to be worked out.

Thank you again for your help.

-Eric

[--CELKO--]

>> I'm using dynamic SQL to execute a pivot query for a sales report since the column names will change by month. <<

Have you thought about BASICS? First of all there is no such thing as
a "pivot query"; look at the defintion of a query. This term is
Microsoft crap.

Dynamic SQL says that you are a realllly baaad SQL programmer with a
realllly baad schema.

In a properly designed system, the display work is done in a reporting
layer by the right tool. Can you afford one (they are cheap)? Can you
write a host language program yourself?

Your approach is totally wrong and will mess up your company; but you
will get kludges on line that will give you enough time to update your
resume. Since I have
3 decades cleaning this stuff, I should be happy.

[Erland Sommarskog]

new DBA in '09 (ericb...@gmail.com) writes:

Another hint is to compose the pivot query statically at first
with some sample data, so you can see how the generated query
should look like.

Maybe the final query should be something like:

WITH pivotquery AS (
-- Pivot query goes here
)
SELECT ...
FROM Pivotquery
JOIN othertable ON ...

Which logically is very similar to what you asked for, but rtather
than joining the results from the dynamically built query, you need
to put the join in the dynamic query.

But as I said, first play with this as a static query, so that you
get the syntax right. Then you should be able find out how to
build the completely query. Actually, that is just:

SELECT @sql = 'WITH pivotquery AS (' + @pivotquery + ')
SELECT ... '

[Erland Sommarskog]

--CELKO-- (jcel...@earthlink.net) writes:
> Have you thought about BASICS? First of all there is no such thing as
> a "pivot query"; look at the defintion of a query. This term is
> Microsoft crap.

Joe: this group is devoted to Microsoft crap. Think about the BASICS
before you spew on your keyboard.

> Dynamic SQL says that you are a realllly baaad SQL programmer with a
> realllly baad schema.

No, it says in this case that the relational model that SQL is built
on for good or bad has a shortcoming in that it cannot address the
common need for an output there the columns is based on the data.

> Your approach is totally wrong and will mess up your company; but you
> will get kludges on line that will give you enough time to update your
> resume. Since I have
> 3 decades cleaning this stuff, I should be happy.

If you go on like this I can sooner or later put "three decades
of cleaning up after Joe Celko's posts in the newsgroups" on my CV.

[Tony Rogerson]

> No, it says in this case that the relational model that SQL is built
> on for good or bad has a shortcoming in that it cannot address the
> common need for an output there the columns is based on the data.

The relational model is sound Erland, it's SQL that is the problem here.

SQL is a very very bad language, it's been built not with the relational
model in mind but through committees with vested interests etc....

Tony.

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D95EB92F...@127.0.0.1...

[Tony Rogerson]

What complete twoddle from a drunk man in front of a keyboard.

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:3b2181ba-289d-447a...@d8g2000yqf.googlegroups.com...

[new DBA in '09]

CELKO, You've given me good advice in the past, but I don't see what
advice you're offering here. I agree that dynamic SQL should be the
last option, but I disagree that it means the schema is bad. I'll
entertain the possibility that I could just be a bad SQL programmer if
you can tell me how to write a pivot query that will dynamically
decide column names in the results as the months pass. In other
words, "Month 1," Month 2," and "Month 3" aren't viable column names;
the last three full months are valid column names, but how to set them
in a pivot query? I'm using this page as a guide: <a href="http://
www.tsqltutorials.com/pivot.php">http://www.tsqltutorials.com/pivot.php</a>.
What is the correct approach, since my approach is wrong?

[new DBA in '09]

Erland, just wanted to let you know that your first advice--to include
the joins in the pivot query from the start--worked terrifically. The
error of duplicated rows I had was due to a historic column value I
was pulling out of a customer activity table when I should have been
pulling static info from the customer table itself. Once I figured
this out, the results came back beautifully. It's still a dynamic SQL
query, which I still don't know how to avoid (considering the column
names will change weekly), but it works, I'm happy, and so's the boss.

What soap opera is playing out in this newsgroup with CELKO? His
advice has helped me before, but this time I can hardly consider his
response "advice." I've seen numerous threads where his advice is
shot down, refuted, or just picked to pieces.

[Erland Sommarskog]

new DBA in '09 (ericb...@gmail.com) writes:

> What soap opera is playing out in this newsgroup with CELKO? His
> advice has helped me before,

Occasionally Joe behaves civilised and actually post useful answers.
Alas, those postings are outweight by all useless and venomous crap
he posts. Apparently last time you met him on a lucky day.

[--CELKO--]

The right way to do a report is to use a report writer. If you have a
medium to large company, then look at a report server. Two companies
with I have consulted had copies of Report Services as part of their
SQL Server contract, but never used it. The excuse was that nobody had
gotten training. Isn't there a “..for Dummies”, on-line training
courses or a contractor looking for a quick job?

If you have to stay in SQL, then set up a table of reporting periods.
The skeleton looks like this:

CREATE TABLE Report_Periods
(report_period_name VARCHAR (25) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date <= period_end_date),
report_period_type CHAR(5) NOT NULL
CHECK (report_period_type IN (..)),
etc);

The report_period_type tells you if this is fiscal, marketing, sales
or whatever.

Use a predicate like “my_date BETWEEN period_start_date AND
period_end_date” to put each row into the right report period. Then
use “GROUP BY report_period_name” with ROLLUP, CUBE and grouping sets
for the fancier stuff.

You can overlap report periods (the “Bikini Madness 2010” promo
occurred at the end of “2010-Q3” and the start of “2010-Q4”). You can
use a fiscal calendar with irregular units of measure. All kinds of
options with fully Standard, portabel code.

But the most important feature is that the report periods are
documented and share by EVERYONE in the enterprise.

[new DBA in '09]

Thanks, CELKO, you've given me some homework to do. I've never used
ROLLUP or CUBE for grouping. I already have a Calendar table, so I
need to think longer about whether or not I also need a Report_Periods
table. My Calendar table has a row for each date, whereas
Report_Periods appears to get one row per reporting period. (So I
guess I just got my answer: I need a Report_Periods table.)

Your help is appreciated. Also, your advice on Reporting Services is
not lost on me. Exposing reporting services reports in a portal is
part of the end-goal.

-Eric

[--CELKO--]

>> Thanks, CELKO, you've given me some homework to do.  I've never used ROLLUP or CUBE for grouping.  <<

I cover this in some of my books, but there are two steps in learning
them:
1) Wow! This neat! Cool! I will do everywhere!
2) Oh, I forgot how to handle the generated NULLs and need to clean up
the "newbie crap" I just wrote while on a roll.

>> I already have a Calendar table, so I need to think longer about whether or not I also need a Report_Periods table. <<

1) You are ahead of most SQL Server guys who are still stuck in a
world of proprietary temporal functions. It is mostly ignorance about
auxiliary tables, but there are others like Tony who like to write
"job secure programs" with the excuse that you should not tier the
architecture, but do things whenever it occurs to you.

2) The Periods table is not the same as a Calendar table. The Calendar
table has a row for each date, whereas Report_Periods has one row per
reporting period. They are at different levels of aggregation. Trees
are not a forest.

You cannot combine them easily -- a single date can be in a zillion
different periods. Do not even try. (So I guess I just got my answer:
I need a Report_Periods table.) Yep.

>> Exposing reporting services reports in a portal is part of the end-goal. <<

Also, look at the commercial and open-source reporting stuff. I have
been a fan of a tiered architectures for decades, but I was really
surprised at the power of a report server.

I have said that DB people need to get a normalized result set and
"throw it over the wall" for decades. But I was throwing very
specific, sorted stuff over my wall. Now I am told that I do not need
to worry about the ORDER BY because the report server can sort faster
and better.

In the old days, I would do (k) different but similar queries with
different sorts. Now, you do one unsorted general query, send it to
the report server, and let the parallelism in the report server do (k)
sorts and (k) filters at once. Meanwhile, the DB server is doing other
stuff.