**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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
CrashmanCommented:
the pivot works separately?
i have doubts  for the column name Value because this is a reserved word
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
ZberteocCommented:
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

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
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
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 2008

From novice to tech pro — start learning today.