**URGENT** SQL PIVOT with UNION

I have an urgent request to solve this problem.  I am trying to create a union with a pivot using the following:


Where am I going wrong?


DECLARE @columns VARCHAR(8000)
        SELECT @columns = COALESCE(@columns + ',[' + JobClass  + ']', '[' + JobClass + ']')
        FROM    SC_WFA_Attrition_wOPS 
        GROUP BY JobClass
        ORDER BY JobClass
        
DECLARE @query VARCHAR(8000)
DECLARE @query2 VARCHAR(8000)

SET @query = ' SELECT *  FROM (SELECT Period, Scorecard,  BusinessCategoryDesc, EntityDesc, BusinessAreaDesc, Category, JobClass, Value
FROM         dbo.SC_WFA_Attrition_wOPS where level = ''tenure'' and category not like ''%Hi%'' and category not like ''%key%'' and category not like ''Overall%'')p
        PIVOT
         (
         sum(Value)
         FOR [JobClass]
         IN (' + @columns + ')
         )
         AS p         
         ORDER BY SCORECARD, period, category'
 

SET @query2 = ' SELECT *  FROM (SELECT Period, Scorecard,  BusinessCategoryDesc, EntityDesc, BusinessAreaDesc, Category, JobClass, Value
FROM         dbo.SC_WFA_Attrition_wOPS where level = ''tenure'' and category not like ''%Hi%'' and category not like ''%key%'' and category  like ''Overall%'')p
        PIVOT
         (
         max(Value)
         FOR [JobClass]
         IN (' + @columns + ')
         )
         AS p         
         ORDER BY SCORECARD, period, category'

set @query =  @query union  @query2

EXECUTE(@query)

Open in new window

rsaphierAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
Instead of this

set @query =  @query union  @query2


do this:


set @query =  @query +' UNION
'+  @query2


and first test with

print @query instead of EXEC(@query).

WIth print you can copy the statement that is generated by your dynamic query and test it first to see if it works.
0
 
CrashmanCommented:
Try

set @query =  @query + ' union ' +  @query2

EXECUTE(@query)
0
 
LowfatspreadCommented:
well try this to start with....

set @query =  @query +' union '+ @query2
print @query


are you sure that you want to union rather than union all?

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
CrashmanCommented:
the pivot works separately?
i have doubts  for the column name Value because this is a reserved word
0
 
rsaphierAuthor Commented:
The pivot does work seperately ... no issues.  Both above suggestions return :

"Incorrect syntax near the keyword 'union'."
0
 
LowfatspreadCommented:
you need extra quotes around tenure etc...

add another 2 each side

ie  ''''tenure'''' rather than ''tenure''

since you are still concatenating the strings....
0
 
CrashmanCommented:
the two querys as the same, is this correct?
but well.

you can try this too.

make the union before...in the internal query

DECLARE @columns VARCHAR(8000)
SELECT  @columns = COALESCE(@columns + ',[' + JobClass + ']',
                            '[' + JobClass + ']')
FROM    SC_WFA_Attrition_wOPS
GROUP BY JobClass
ORDER BY JobClass
        
DECLARE @query VARCHAR(8000)
DECLARE @query2 VARCHAR(8000)

SET @query = ' SELECT *  FROM (

SELECT Period, Scorecard,  BusinessCategoryDesc, EntityDesc, BusinessAreaDesc, Category, JobClass, Value
FROM         dbo.SC_WFA_Attrition_wOPS where level = ''tenure'' and category not like ''%Hi%'' and category not like ''%key%'' and category not like ''Overall%''

union all

SELECT Period, Scorecard,  BusinessCategoryDesc, EntityDesc, BusinessAreaDesc, Category, JobClass, Value
FROM         dbo.SC_WFA_Attrition_wOPS where level = ''tenure'' and category not like ''%Hi%'' and category not like ''%key%'' and category  like ''Overall%''

)
p
        PIVOT
         (
         sum(Value)
         FOR [JobClass]
         IN (' + @columns + ')
         )
         AS p         
         ORDER BY SCORECARD, period, category'

--set @query =  @query union  @query2

EXECUTE ( @query
       )

Open in new window


if you have other query for the second, must take care of the columns name
0
 
LowfatspreadCommented:
remove the 0rder by from the first query
you can only have 1 order by at the end of a union (unless you are using top ... and sub queries)
0
 
rsaphierAuthor Commented:
Perfect ... that helped me to identify the problem which was the order by in the order by .. it needed to be removed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.