Another pivot table from sql

what date do you want?
the max(planned date) ?

I would like the max planned and max actual

You have attached the original data going vertically, but how do you want the end result (horizontal / pivot data) to look?
For a background on PIVOT keyword and usage in SQL Server 2005/2008, please read the following article by Mark Wills.
Dynamic Pivot Procedure for SQL Server - http:A_653.html.

I have seen the article before, I however have trouble following it.  Must be my inexperience.  I would like the format to look like this:

Material Number   Geographic Country  (and then the Global Event and date columns)


does that make sense?

Can you show with actual data, as to clear up what you want pivoted.
i.e.,
110936-B21 AU ES 2005-12-08 2005-12-08
110936-B21 AU PA 2004-12-10 2004-12-10
110936-B21 ID ES 2005-12-08 2005-12-08
Does this get pivoted to:
110936-B21  AU ES-2005-12-08 ES-2005-12-08 PA-2004-12-10 PA-2004-12-10 ...
 

I have attached a sample output that I would like.  However, I would like to show all Global events as separate columns.  I think your sample output is correct.
output1.txt

Okay, here you go with dynamic portion and all.
You won't need all the code below as I needed to duplicate your environment to test.  Therefore, please follow the comments in the code as to what can be removed and more importantly what should be changed.

/***** begin: you won't need this portion *****/
with t([Material Number], [Geographic Hierarchy Country], [Global Event Code], [Planned Date], [Actual Date])
as
(
select '110936-B21', 'AU', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'AU', 'PA', '2004-12-10', '2004-12-10' union
select '110936-B21', 'ID', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'IN', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'KR', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'MY', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'NZ', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'NZ', 'PA', '2005-03-10', '2005-03-10' union
select '110936-B21', 'PH', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'SG', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'TH', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'TW', 'ES', '2005-12-08', '2005-12-08' union
select '110936-B21', 'TW', 'PA', '2003-04-14', '2003-04-14' union
select '110936-B24', 'AU', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'AU', 'PA', '2004-12-10', '2004-12-10' union
select '110936-B24', 'ID', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'IN', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'KR', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'MY', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'NZ', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'NZ', 'PA', '2005-03-10', '2005-03-10' union
select '110936-B24', 'PH', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'SG', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'TH', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B24', 'TW', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'AU', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'AU', 'PA', '2004-12-10', '2004-12-10' union
select '110936-B25', 'ID', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'IN', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'KR', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'MY', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'NZ', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'NZ', 'PA', '2005-03-10', '2005-03-10' union
select '110936-B25', 'PH', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'SG', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'TH', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'TW', 'ES', '2006-02-01', '2006-02-01' union
select '110936-B25', 'TW', 'PA', '2003-04-14', '2003-04-14' union
select '118003-B39', 'ID', 'ES', '2004-07-01', '2004-07-01' union
select '118003-B39', 'IN', 'ES', '2004-09-18', '2008-09-21' union
select '118003-B39', 'KR', 'ES', '2004-07-01', '2004-07-01' union
select '118003-B39', 'PH', 'ES', '2004-07-01', '2004-07-01' union
select '118003-B39', 'SG', 'ES', '2004-07-01', '2004-07-01' union
select '118003-B39', 'TH', 'ES', '2004-07-01', '2004-07-01' union
select '118003-B39', 'TW', 'ES', '2004-07-01', '2004-07-01' union
select '118003-B39', 'VN', 'ES', '2004-07-01', '2004-07-01' union
select '120300-003', 'IN', 'ES', '2008-01-30', '2008-01-31' union
select '120300-003', 'IN', 'PA', '2002-12-01', '2002-12-01' union
select '120300-003', 'MY', 'ES', '2008-01-30', '2008-01-31' union
select '120300-003', 'MY', 'PA', '2002-12-01', '2002-12-01'
)
select *
into #tmp_for_pvt
from t
;
/***** end of: you won't need this portion *****/

DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(max)

-- ***** REPLACE #tmp_for_pvt with your real table name! *****
SET @Columns = substring((select ',['+[Global Event Code]+']' from #tmp_for_pvt group by [Global Event Code] for xml path('')),2,8000)
SET @SQL = 'select coalesce(p_pvt.[Material Number], a_pvt.[Material Number]) as [Material Number]
     , coalesce(p_pvt.[Geographic Hierarchy Country], a_pvt.[Geographic Hierarchy Country]) as [Geographic Country]
     , '+replace(@columns,']', '_Planned]')+'
     , '+replace(@columns,']', '_Actual]')+'
from (
   select [Material Number], [Geographic Hierarchy Country]
        , [Global Event Code]+''_Planned'' as EventCode
        , [Planned Date]
   from #tmp_for_pvt
) pln
pivot (MAX([Planned Date]) FOR EventCode IN ('+replace(@columns,']', '_Planned]')+')) p_pvt
full outer join (
   select [Material Number], [Geographic Hierarchy Country]
        , [Global Event Code]+''_Actual'' as EventCode
        , [Actual Date]
   from #tmp_for_pvt
) act
pivot (MAX([Actual Date]) FOR EventCode IN ('+replace(@columns,']', '_Actual]')+')) a_pvt
on a_pvt.[Material Number] = p_pvt.[Material Number] 
   and a_pvt.[Geographic Hierarchy Country] = p_pvt.[Geographic Hierarchy Country]'
EXEC(@sql);

-- this is not needed either
drop table #tmp_for_pvt;

Select allOpen in new window

FYI, the replace() functionality here is to allow the use of one @columns variable that just lists all the valid event codes that I then convert to EC_Planned and EC_Actual depending on the context.

Hi,

Can I join in ? *laughing* can I also borrow that build of the test table (#tmp_for_pvt) ?

Because there are really only two columns - planned or actual, then we can effectively "unpivot" first to flatten the structure first up. For example....

SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+'_Actual' as EventCode, [Actual Date] as EventDate from #tmp_for_pvt
union
SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+'_Planned' as EventCode, [Planned Date] as EventDate from #tmp_for_pvt

Notice how we are encapsulating the global event code into a new column "EventCode" and simply cast date as the EventDate.

Now, we have a situation where we simply need to pivot EventDate over the range of EventCodes (ie the values that become new column headings)

So, using the sample data and "hardcoding" the expected columns as a result of the pivot, we get :

SELECT *
From

   (SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+'_Actual' as EventCode, [Actual Date] as EventDate from #tmp_for_pvt
    union
    SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+'_Planned' as EventCode, [Planned Date] as EventDate from #tmp_for_pvt) as src

PIVOT

   (max(eventdate) FOR Eventcode IN (PA_Planned, PA_Actual, ES_Actual, ES_Planned) )AS pvt

order by 1,2
GO



Now, we still have the challenge of "What makes up the actual columns of the new pivot result" - in this case we simply hardcoded : (PA_Planned, PA_Actual, ES_Actual, ES_Planned)

The advantage is we can actually sequence the way we want them to appear, and if not too many event codes, then can be just as easy to hard code.

However, if there are "unknown" values for event codes (or just too many :) ), then we do have to go to dynamic SQL

In this case, (or at least for the example), it can be readily achieved by :


DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + [Global Event Code]+'_Actual,' + [Global Event Code]+'_Planned' from #tmp_for_pvt group by [Global Event Code]

exec ('SELECT *
From

   (SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Actual'' as EventCode, [Actual Date] as EventDate from #tmp_for_pvt
    union
    SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Planned'' as EventCode, [Planned Date] as EventDate from #tmp_for_pvt) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')

Mark, where in the code would I place the INTO statement so I can put the results into a table?

You would put it in the final select before the FROM.
'select coalesce(p_pvt.[Material Number], a_pvt.[Material Number]) as [Material Number]
     , coalesce(p_pvt.[Geographic Hierarchy Country], a_pvt.[Geographic Hierarchy Country]) as [Geographic Country]
     , '+replace(@columns,']', '_Planned]')+'
     , '+replace(@columns,']', '_Actual]')+'
into your_new_table
from (
   select [Material Number], [Geographic Hierarchy Country]
        , [Global Event Code]+''_Planned'' as EventCode
        , [Planned Date]
   from #tmp_for_pvt
) pln
pivot (MAX([Planned Date]) FOR EventCode IN ('+replace(@columns,']', '_Planned]')+')) p_pvt
full outer join (
   select [Material Number], [Geographic Hierarchy Country]
        , [Global Event Code]+''_Actual'' as EventCode
        , [Actual Date]
   from #tmp_for_pvt
) act
pivot (MAX([Actual Date]) FOR EventCode IN ('+replace(@columns,']', '_Actual]')+')) a_pvt
on a_pvt.[Material Number] = p_pvt.[Material Number]
   and a_pvt.[Geographic Hierarchy Country] = p_pvt.[Geographic Hierarchy Country]'
EXEC(@sql);

 or
exec ('SELECT *
into your_new_table
From

   (SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Actual'' as EventCode, [Actual Date] as EventDate from #tmp_for_pvt
    union
    SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Planned'' as EventCode, [Planned Date] as EventDate from #tmp_for_pvt) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')
 

Like what mwvisa1 says above....

Just remember that "your_new_table" must be non-existent at the time of running the query.

Alternatively, and if you know the column names that you will use, you could have a "permanent" table and insert into that... e.g.

-- first create the table

create table my_perm_pivot_results ( [Material Number] varchar(20), [Geographic Hierarchy Country] char(3), PA_Planned datetime, PA_Actual datetime, ES_Actual datetime, ES_Planned datetime)

-- now run our query

INSERT my_perm_pivot_results ([Material Number], [Geographic Hierarchy Country], PA_Planned, PA_Actual, ES_Actual, ES_Planned)
SELECT [Material Number], [Geographic Hierarchy Country], PA_Planned, PA_Actual, ES_Actual, ES_Planned
From

   (SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+'_Actual' as EventCode, [Actual Date] as EventDate from #tmp_for_pvt
    union
    SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+'_Planned' as EventCode, [Planned Date] as EventDate from #tmp_for_pvt) as src

PIVOT

   (max(eventdate) FOR Eventcode IN (PA_Planned, PA_Actual, ES_Actual, ES_Planned) )AS pvt

order by 1,2
GO

-- now lets see

select * from my_perm_pivot_results



Certainly for "dynamic" columns, the "select ... into <newtable> from <datasource>" is probably best. And because it is within the dynamic SQL then you could set up a variable for the table name and string it in (in a similar way as the @columns variable).

For reporting purposes, where columns should really be "known" then you might prefer to "hardcode" the columns, and then a permanent table becomes more attractive. Then again, for reporting purposes you might use a matrix or tablix type structure and simply use the unpivotted data as the source and let the report manage the "pivot" part,

Does that make sense ?

Also, dont forget to vote on both Articles mentioned by mwvisa1 :)
 
https://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Broaden-Your-Horizons-Pivot-Again.html
https://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html

And if not clear, then post a comment in the article so we can help clarify for you :)

I am receiving an error when I run the attached code:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'PE:'.
Msg 132, Level 15, State 1, Line 11
The label 'PE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PA' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'NE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'ES' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'ES' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'ES' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'NE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'NE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PA' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PA' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PA' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PA' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'PE' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'ES' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 11
The label 'ES' has already been declared. Label names must be unique within a query batch or stored procedure.

DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + [Global Event Code]+'_Actual,' + [Global Event Code]+'_Planned' from ZWPLCSMEV_AP group by [Global Event Code]

exec ('SELECT * 
INTO ZWPLCS_PIVOT
From

   (SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Actual'' as EventCode, [Actual Date] as EventDate from ZWPLCSMEV_AP
    union
    SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Planned'' as EventCode, [Planned Date] as EventDate from ZWPLCSMEV_AP) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')

Select allOpen in new window

Some of my global event codes are more than 2 characters.

ED
ES
ES:SN
ES:SY
ES:WR
GA
NE:PB
NE:PG
PA
PA:AD
PA:SR
PA:WR
PE
PE:PF
PE:PS
PE:PW
SA

Did you run my code here - http:#a32648344 ? Trying to figure out if you are getting the error with both sets of code as tested mine extensively before posting but not with new information you just added, so would have to test if you are getting that error with my code. Please let me know.

Okay, tested with mine and didn't get that error.  Got the error using Mark's code as you will need to add the brackets since your values have the ':' in it.
 

DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[Global Event Code]+'_Actual],[' + [Global Event Code]+'_Planned]' from #tmp_for_pvt group by [Global Event Code]

exec ('SELECT * 
From

   (SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Actual'' as EventCode, [Actual Date] as EventDate from #tmp_for_pvt
    union
    SELECT [Material Number], [Geographic Hierarchy Country], [Global Event Code]+''_Planned'' as EventCode, [Planned Date] as EventDate from #tmp_for_pvt) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')
;

Select allOpen in new window

Yep, mwvisa1 has it correct.

The new column names must be encapsulated in square brackets - sorry about that thought they were all like "ES" and "PA" (what is that saying about assume ? )

So, as mwvisa1 points out, need to add in the square brackets in the creation of the column names...

DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[Global Event Code]+'_Actual],[' + [Global Event Code]+'_Planned]' from ZWPLCSMEV_AP  group by [Global Event Code]