PIVOT in query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PIVOT in query?

Author

Topic

towardabettercountry
Starting Member

26 Posts

Posted - 2011-10-17 : 17:37:36

I'm stuck on the below query. I would like to display the results of FSourceCode as columns and somehow show when they aren't NULL. (The result can be one of three things: DSub, DUSub, DPlus). I've tried using PIVOT, but I don't think I have the right syntax. Can anyone provide any assistance? Thank you!!

SELECT TOP 10
SyPerson.SSN,
SyPerson.LastName,
SyPerson.FirstName,
SyPerson.SyStudentID,
SyPerson.StuNum,
FaDoc12.DateAdded,
EventSubject,
ProgInst,
FSourceCode

FROM
SyPerson left outer join
dbo.FaDoc12 on SyPerson.SyPerson = FaDoc12.SyPersonID left outer join
cst_DetailActivity_Status_vw on SyPerson.SyPersonID = ccst_DetailActivity_Status_vw.SyPersonID left outer join
rpt_DLanalysis_vw on SyPerson.SyPersonID = rpt_DLanalysis_vw.SyPersonID;

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:46:10

do you mean this?


SELECT *
FROM
(
SELECT TOP 10 
SyPerson.SSN,
SyPerson.LastName, 
SyPerson.FirstName, 
SyPerson.SyStudentID,
SyPerson.StuNum,
FaDoc12.DateAdded,
EventSubject,
ProgInst,
FSourceCode

FROM 
SyPerson left outer join
dbo.FaDoc12 on SyPerson.SyPerson = FaDoc12.SyPersonID left outer join
cst_DetailActivity_Status_vw on SyPerson.SyPersonID = ccst_DetailActivity_Status_vw.SyPersonID left outer join
rpt_DLanalysis_vw on SyPerson.SyPersonID = rpt_DLanalysis_vw.SyPersonID;


)t
PIVOT (MAX(FSourceCode) FOR FSourceCode IN ([DSub],[DUSub],[DPlus]))p

if not post some sample data and explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

towardabettercountry
Starting Member

26 Posts

Posted - 2011-10-18 : 09:14:14

That's exactly what I meant

Thank you very much!! Do you know of any good articles that explain PIVOT. I would like to learn. Thank you again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 09:41:34

welcome

see

http://www.tsqltutorials.com/pivot.php

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

towardabettercountry
Starting Member

26 Posts

Posted - 2011-10-18 : 14:22:46

Thanks for the article. Very helpful. One quick question - What do the "t" and the "p" do at the beginning and end? I didn't find similar syntax in any examples online, and just wondered what their significance is. Thanks again for being so willing to help newbies...

quote:
)t
PIVOT (MAX(FSourceCode) FOR FSourceCode IN ([DSub],[DUSub],[DPlus]))p

dmills99
Starting Member

7 Posts

Posted - 2011-10-18 : 15:54:42

the t and p are aliases for code within each parentheses

towardabettercountry
Starting Member

26 Posts

Posted - 2011-10-18 : 16:20:15

quote:
Originally posted by dmills99

the t and p are aliases for code within each parentheses

I promise I won't make this an endless string of questions, so only one more: Why is an alias needed in this situation?

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-10-18 : 17:16:41

Ooh, I know this (kinda) since I just started getting the hang of it - the FROM and PIVOT tables always need an alias, even though you're not using them yourself. They get used behind the scenes.

Somebody please correct me if I'm wrong!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 04:16:07

alias is needed to refer the table which is created by pivoting data from original table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources