Pivot w/o aggregation...query not working

Question

Post reply

Pivot w/o aggregation...query not working

Jon V

Mr or Mrs. 500

Points: 533
More actions
August 27, 2010 at 11:51 pm

#223129

Hi all--Given this query, I need to make this query into a pivot table:
select Personnel_id,
First Name,
Last Name,
E-mail,
Division,
Region,
Country,
Manager,
Question,
Answer,
Date Completed
from #pivot
where
Question Answer
01. Received File No
02. Read File Yes
03. Signed File Yes
...
Needs to look like this:
Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ...
Test01 Test No Yes Yes
I have the following pivot query:
SELECT [Personnel_id]
,[First Name]
,[Last Name]
,[Email]
,[Division]
,[Region]
,[Country]
,[Manager]
,[Question]
,[Answer]
,[Date Completed]
FROM (SELECT [Personnel_id]
,[First Name]
,[Last Name]
,[Email]
,[Division]
,[Region]
,[Country]
,[Manager]
,[Question]
,[Answer]
,[Date Completed]
FROM #pivot) topvt
PIVOT (MAX([Personnel_id]) FOR Answer in
([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt
ORDER BY pvt.[Personnel_id]
I get this error:
Msg 207, Level 16, State 1, Line 83
Invalid column name 'Personnel_id'.
Any ideas?
Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Your pvt subqry does not contain a column named [Personnel_id]. Therefore, your ORDER BY fails.

I think that's not the only flaw in your query...

But without any table def, sample data and expected result it's hard to answer...

Please provide more information as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004659 More actions · Answer 2

You're simple trying to do too much all at once. Do the pivot first... then display it and the other information. It's called "pre-aggregation" (thanks Peter Larsson) and not only will it make your life easier, it'll also make your code faster... much faster.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jon V Mr or Mrs. 500 Points: 533 More actions · Answer 3

CREATE TABLE [dbo].[Qpivot]([Personnel_id] [nvarchar](50) NULL,

[First Name] [nvarchar] (50) NOT NULL,

[Last Name] [nvarchar] (50) NOT NULL,

[Email] [nvarchar] (200) NOT NULL,

[Division] [nvarchar] (50) NULL,

[Region] [nvarchar] (50) NULL,

[Country] [nvarchar] (50) NULL,

[Manager] [nvarchar] (100) NULL,

[Question #] [smallint] NOT NULL,

[Question] [nvarchar] (2000) NOT NULL,

[Answer] [nvarchar] (250) NOT NULL,

[Date Completed Certification] [smalldatetime] NULL )

I'm pulling the data into a temp table from other tables. Here's the near-completed statement:

SELECT [Personnel_id]

,[First Name]

,[Last Name]

,[Email]

,[Division]

,[Region]

,Country

,[Manager]

([01. Received COS]

,[02. Read COS]

,[03. Complied with COS]

,[04. Understand Co Expects Reporting]

,[05. Reported Violations]

,[05a. Reported Violations Text]

,[06. Approval for COS]

,[06a. Approval for Govt COS Text]

,[07. Acted in Spirit and Letter of COS]

,[08. Disclosed COS]

,[08a. Disclosed COI Text]

,[09. Disclosed Board Positions]

,[10. Certify Info Complete])

FROM (select [Personnel_id]

,[First Name]

,[Last Name]

,[Email]

,[Division]

,[Region]

,Country

,[Manager]

,Question

,Answer

,COALESCE(SubmitDate as [Date Completed Certification]

from #ctepvt) as COMpvt

PIVOT (MAX (Answer) FOR Question IN

([01. Received COS]

,[02. Read COS]

,[03. Complied with COS]

,[04. Understand Co Expects Reporting]

,[05. Reported Violations]

,[05a. Reported Violations Text]

,[06. Approval for COS]

,[06a. Approval for Govt COS Text]

,[07. Acted in Spirit and Letter of COS]

,[08. Disclosed COS]

,[08a. Disclosed COI Text]

,[09. Disclosed Board Positions]

,[10. Certify Info Complete])) pvt

ORDER BY [Personnel_id]

Here's a sample of data:

Personnel_id First Name Last Name Email Division Region Country Manager Date Completed

Test44 Test User nomail@comp.net - - - NULL - 2009-07-08 00:00:00

Test64 Test User nomail@comp.net - - - - NULL - NULL

Complicating things is that I have to insert ntext data into another row if a "Not Applicable" answer shows up, appending it from the previous row. Any useful links for that one?

Jeff Moden SSC Guru Points: 1004659 More actions · Answer 4

Jon V (8/30/2010)
Complicating things is that I have to insert ntext data into another row if a "Not Applicable" answer shows up, appending it from the previous row. Any useful links for that one?

Since I'm not sure what you mean, I have to say "no". I understand "previous row" calcs fairly well but I'm not sure what you mean above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)