TSQL - PivotTable

Hi Experts,

I am new to all this and this would be my first pivot table.

I have started but I know there is still work that is needed and a few syntax errors:

Declare 
@Date1 datetime=GETDATE(),
@PivotColumnHeaders VARCHAR(MAX)
SELECT  @PivotColumnHeaders = 
			  COALESCE(
				 @PivotColumnHeaders + N',[' + Search1 + N']',
				 N'[' + Search1 + N']'
			   )
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       
       PIVOT(
                                  MAX(CourseStatus)
                                  FOR Search1 IN (
                                     [PivotColumnHeaders])
              ) P

Open in new window


PivotColumnHeaders I am guessing this is from the @PivotColumnHeaders?  But not sure how it all fits and what I am still missing?

Please help and thanks
Amour22015Asked:
Who is Participating?
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.

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

Open in new window


relevant PAQ's
http://www.experts-exchange.com/questions/28188971/Need-some-help-using-pivot-in-a-query-SQL-2008-R2.html
http://www.experts-exchange.com/questions/28698875/Can-you-please-help-in-below-sql-server-query.html#a40894549
0
PortletPaulfreelancerCommented:
Ok. so for the actual "pivot"

Once upon a time there was no "PIVOT" operator in any variant of SQL but we were still able to pivot data. How? By using aggregate functions and GROUP BY.

Why do I mention this?
Because I think you are going to a lot of trouble just to use the word PIVOT, and that you do not actually need to do this extra effort.

Here is some sample data:
CREATE TABLE Table1
    ([ID] int, [RowCode] varchar(2), [ColCode] varchar(1), [Value] int)
;
    
INSERT INTO Table1
    ([ID], [RowCode], [ColCode],[Value] )
VALUES
    (1, 'R1', 'a', 12),
    (2, 'R2', 'b', 21),
    (3, 'R3', 'c', 14),
    (4, 'R2', 'a', 6),
    (5, 'R2', 'b', 12),
    (6, 'R1', 'c', 9),
    (7, 'R1', 'a', 22),
    (8, 'R3', 'b', 41),
    (9, 'R3', 'c', 6),
    (10, 'R2', 'a', 9)
;

Open in new window

and here is the "pivot" result I want fro that data:
| RowCode | val_a | val_b | val_c |
|---------|-------|-------|-------|
|      R1 |    22 |     0 |     9 |
|      R2 |     9 |    21 |     0 |
|      R3 |     0 |    41 |    14 

Open in new window


OK. so let's use the "PIVOT OPERATOR" to create this.
SELECT
      [RowCode]
    , a
    , b
    , c
FROM (
    SELECT
          [RowCode]
        , [ColCode]
        , [Value]
     FROM table1
    ) sourcedata
pivot
    (
        max([Value])
        FOR [ColCode] IN (a,b,c)
    ) p
    ;

Open in new window

When this is run we get this:
| RowCode |      a |      b |      c |
|---------|--------|--------|--------|
|      R1 |     22 | (null) |      9 |
|      R2 |      9 |     21 | (null) |
|      R3 | (null) |     41 |     14 |

Open in new window

oops, to get rid of those NULLS (which do NOT exist in the data at all, it's becuse of the matrix we form in the pivot) we have to adjust the select clause to the following:
SELECT
      [RowCode]
    , isnull([a],0) as val_a
    , isnull([b],0) as val_b
    , isnull([c],0) as val_c
FROM (
    SELECT
          [RowCode]
        , [ColCode]
        , [Value]
     FROM table1
    ) sourcedata
pivot
    (
        max([Value])
        FOR [ColCode] IN (a,b,c)
    ) p
    ;

Open in new window

Now the output is correct:
| RowCode | val_a | val_b | val_c |
|---------|-------|-------|-------|
|      R1 |    22 |     0 |     9 |
|      R2 |     9 |    21 |     0 |
|      R3 |     0 |    41 |    14 |

Open in new window

THE VERY SAME RESULT is produced by this query:
select
      [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]
;

Open in new window

see: http://sqlfiddle.com/#!6/44bd5/7

{+ edit}
Bottom line. You are already using a GROUP BY in your query and you probably do not need to use the pivot operator to achieve the desired result.
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
Amour22015Author Commented:
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]
;

Open 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Amour22015Author Commented:
Isn't this part a Dynamic SQL:
SELECT  @PivotColumnHeaders = 
			  COALESCE(
				 @PivotColumnHeaders + N',[' + Search1 + N']',
				 N'[' + Search1 + N']'
			   )

Open 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 

Open in new window


And this part the Pivot Table:
PIVOT(
                                  MAX(CourseStatus)
                                  FOR Search1 IN (
                                     [PivotColumnHeaders])
              ) P

Open 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
0
Amour22015Author Commented:
Great Thanks
0
PortletPaulfreelancerCommented:
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
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.

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.