asked on

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

Haver Ramirez

Try

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

EXECUTE(@query)

Lowfatspread

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?

Haver Ramirez

the pivot works separately?
i have doubts for the column name Value because this is a reserved word

rsaphier

ASKER

The pivot does work seperately ... no issues. Both above suggestions return :

"Incorrect syntax near the keyword 'union'."

Lowfatspread

you need extra quotes around tenure etc...

add another 2 each side

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

since you are still concatenating the strings....

Haver Ramirez

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

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

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)

rsaphier

ASKER

Perfect ... that helped me to identify the problem which was the order by in the order by .. it needed to be removed.

**URGENT** SQL PIVOT with UNION

URGENT SQL PIVOT with UNION