TSQL - PivotTable

Well the first thing I have tell you is that you CANNOT insert a variable containing SQL into a query the way you have.

e.g. you simply CANNOT do this:

declare @cols nvachar(max) = 'id, column1, columns2'
select @cols from table1

Why?
Well no matter how many commas that @cols variable has in it, or what the variable is named, it is just ONE STRING as far as the dbms is concernd, NOT sql, NOT many intelligent subparts. Just a string.

Imagine if I did this:

declare @cols nvachar(max) = 'the quick brown fox jumped of the lazy sql'
select @cols from table1

as far as the dbs is concerned it is EXACTLY THE SAME ISSUE

@cols is a STRING not a set of sql statements. You make it look the same, but it isn't the same.

----
That's the bad news.

---
Now the good news.

All the bad news is true, UNLESS you make an ENTIRE SQL STATEMENT and THEN execute it.

Like this:

declare @query varchar(max) = 'select id, column1, columns2 from table1'
exec(@query)

This is called "dynamic sql" and it works, but it does have security risks (called "sql injection") so use it wisely.

{+edit}
Here is an example "dymanic sql" use of pivot. Please ignore the use row_number() it was necessary for a specific question

    DECLARE @cols AS NVARCHAR(MAX)
    DECLARE @query  AS NVARCHAR(MAX)

    SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(s.state)
                FROM table1 s
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

    SET @query = 'SELECT rn, ' + @cols + ' FROM
                (
                    SELECT
                          [state]
                        , [city]
                        , row_number() over(partition by [State] order by [City] ASC) as rn
                     FROM table1
               ) sourcedata
                pivot
                (
                     max([city])
                    FOR [state] IN (' + @cols + ')
                ) p '

    --select @query -- use select to inspect the generated sql
    execute(@query) -- once satisfied that sql is OK, use execute

Select allOpen in new window

relevant PAQ's
https://www.experts-exchange.com/questions/28188971/Need-some-help-using-pivot-in-a-query-SQL-2008-R2.html
https://www.experts-exchange.com/questions/28698875/Can-you-please-help-in-below-sql-server-query.html?anchorAnswerId=40894549#a40894549

Is this not a manual pivot:

elect
      [RowCode]
    , max(case when [ColCode] = 'a' then [value] else 0 end) as val_a
    , max(case when [ColCode] = 'b' then [value] else 0 end) as val_b
    , max(case when [ColCode] = 'c' then [value] else 0 end) as val_c
from Table1
group by
      [RowCode]
;

Select allOpen in new window

My problem is that I can not do a manual pivot because "[ColCode]" would go on and on and on.  So the way you showed this I would have to go in each time someone entered a new [ColCode] and add that to the SP.  This is the whole reason why I need to use a pivot.

In my first post I have @PivotColumnHeaders = Search1
well Search1 would be your [ColCode]  because it would go etc......................

Please help and thanks.

Isn't this part a Dynamic SQL:

SELECT  @PivotColumnHeaders = 
			  COALESCE(
				 @PivotColumnHeaders + N',[' + Search1 + N']',
				 N'[' + Search1 + N']'
			   )

Select allOpen in new window

And this part would have to be placed in that Dynamic SQL:

FROM (
       SELECT     tblNAsContacts.Contact, tblNAsContacts.LastName, tblNAsContacts.FirstName, tblNAsContacts.CompanyName,
                           fncUtlContactName.NameSort, tblNAsEvents.Search1, 'Completed' AS CourseStatus,
                                  MIN(tblNAsAffiliations.Date1) AS MinClassDate, MAX(tblNAsAffiliations.Date1) AS MaxClassDate
       FROM        tblNAsAffiliations INNER JOIN 
                                  tblNAsContacts ON tblNAsAffiliations.Contact1 = tblNAsContacts.Contact INNER JOIN
                                  dbo.fncUtlContactName() ON tblNAsContacts.Contact = fncUtlContactName.Contact INNER JOIN
                                  tblNAsEvents ON tblNAsAffiliations.EventID = tblNAsEvents.EventID INNER JOIN   
                                                  (SELECT     tblNAsAffiliations.Contact1 AS Contact
                                                       FROM          tblNAsAffiliations INNER JOIN
                                                                                            dbo.fncUtlMemberFolder() AS fncUtlMemberFolder ON tblNAsAffiliations.Contact2 = fncUtlMemberFolder.Contact1
                                                       WHERE      (tblNAsAffiliations.Affiliation = N'Work') AND (fncUtlMemberFolder.Code1 = N'Member') AND (fncUtlMemberFolder.Date2 >= DATEADD(m, -6, @Date1))
                                                       UNION
                                                       SELECT     Contact1 AS Contact
                                                       FROM         dbo.fncUtlGovernance(@Date1)
                                                       WHERE     (GovernancePositionCode = 'BIAStaff')
                                                       GROUP BY Contact1) AS EXAMINEES ON tblNAsAffiliations.Contact1 = EXAMINEES.Contact
       WHERE     (tblNAsAffiliations.Affiliation = 'Course') AND
                                  (tblNAsEvents.Meeting = 'BU') AND
                                  (tblNAsEvents.Subtype = 'BU') 
       GROUP BY tblNAsContacts.Contact, tblNAsContacts.LastName, tblNAsContacts.FirstName, tblNAsContacts.CompanyName,
                           fncUtlContactName.NameSort, tblNAsEvents.Search1
       ) AS PivotData 

Select allOpen in new window

And this part the Pivot Table:

PIVOT(
                                  MAX(CourseStatus)
                                  FOR Search1 IN (
                                     [PivotColumnHeaders])
              ) P

Select allOpen in new window

So what I am saying is that I would need help with constructing to get the end result?

My end result would be and export to excel (I already do that part)
Columns A - G would be just a select off some join Tables (which are the same as the Query posted).
But:
G - ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
would come from the Pivot Table Part where Search1 is what ever the person inputs.
So if I input into Search1:
BU001
BU002
BU003
BU004
Bu005
Then in excel
Columns H, I, J, K, L would have a header as:
H = BU001 with all the data from that record going down the column
I = BU002 with all the data from that record going down the column
J = BU003 with all the data from that record going down the column
K = BU004 with all the data from that record going down the column
L = BU005 with all the data from that record going down the column
And so on and on and on and on

I hope that makes sense?

Please help and thanks

Great Thanks

I guess you got what you needed.

In summary I was trying to indicate:
a. you need to formulate the whole dynamic query into a string, then execute that string.
b. you an dynamically create a "manual pivot" query and you are not forced to use the pivot operator