how to join dynamic SQL results with another query
in [Programming SQL]
|
Prev: DB2 on SS
Next: SSRS doesnt like Guid param datatypes
From: new DBA in '09 on 9 Jun 2010 19:57 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.
From: Erland Sommarskog on 10 Jun 2010 16:51 new DBA in '09 (ericbragas(a)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. -- Erland Sommarskog, SQL Server MVP, esquel(a)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
From: new DBA in '09 on 11 Jun 2010 20:02 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
From: Erland Sommarskog on 12 Jun 2010 17:06 new DBA in '09 (ericbragas(a)gmail.com) writes: > 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. 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, SQL Server MVP, esquel(a)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
From: new DBA in '09 on 15 Jun 2010 16:18
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? |