Pivoting Help

Question

Post reply

Pivoting Help

yogi123

SSCrazy

Points: 2932
More actions
November 13, 2012 at 9:26 am

#270154

Hello
Please help me to do this
CREATE TABLE #X1
(vcEmployeeUniqueID VARCHAR(20),
vcLastName VARCHAR(20),
vcFirstName VARCHAR(20),
vcSchoolName VARCHAR(50),
iSchoolYearCode INT)
INSERT INTO #X1 VALUES ('001210214','Miller','Helen','NORWALK HIGH SCHOOL',2009)
INSERT INTO #X1 VALUES ('001210214','Miller','Helen','STAMFORD HIGH SCHOOL',2010)
INSERT INTO #X1 VALUES ('001210214','Miller','Helen','NORWALK HIGH SCHOOL',2011)
INSERT INTO #X1 VALUES ('003215847','Carolyn','June','NORWALK HIGH SCHOOL',2010)
INSERT INTO #X1 VALUES ('003215847','Carolyn','June','WATERBURY HIGH SCHOOL',2011)
INSERT INTO #X1 VALUES ('002149013','Steve','Ulman','STAMFORD HIGH SCHOOL',2011)
Desired output will be
vcEmployeeUniqueIDvcLastNamevcFirstNameSY2009SY2010SY2011
001210214MillerHelenNORWALK HIGH SCHOOLSTAMFORD HIGH SCHOOLNORWALK HIGH SCHOOL
003215847CarolynJuneNORWALK HIGH SCHOOLWATERBURY HIGH SCHOOL
002149013SteveUlmanSTAMFORD HIGH SCHOOL
Please help me to do this.

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Looks like homework to me.

Have you tried using PIVOT. Where are you so far?

We can help you to learn and resolve issues instead of just providing straight solutions, which you will not understand or remember...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

yogi123 SSCrazy Points: 2932 More actions · Answer 2

Eugene Elutin (11/13/2012)
Looks like homework to me.
Have you tried using PIVOT. Where are you so far?
We can help you to learn and resolve issues instead of just providing straight solutions, which you will not understand or remember...

Thanks for Reply

I try this

Select vcEmployeeUniqueID, [2009],[2010],[2011]

from

( Select vcEmployeeUniqueID,

vcLastName,

vcFirstName,

vcSchoolName,

iSchoolYearCode from #X1) as sourcetable

Pivot ( count(vcSchoolName) for iSchoolYearCode in ([2009],[2010],[2011])) as pivotable

and i got output as

vcEmployeeUniqueID200920102011

001210214111

002149013001

003215847011

but instead of 1 and 0 , i need to display vcSchoolName or Blank

so please help me to do this

That will be really appreciated

and This is not a Home Work.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

You are almost there!

just use MAX instead of COUNT 🙂

also you don't need to sub-query select of all columns from #X:

...

FROM #X1 p

PIVOT ...

will do just fine!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

BTW. Formatting your query nice, improves its readability and therefore its maintainability:

SELECT vcEmployeeUniqueID

,vcLastName

,vcFirstName

,[2009] AS SY2009

,[2010] AS SY2010

,[2011] AS SY2011

FROM #X1 AS src

PIVOT (MAX(vcSchoolName)

FOR iSchoolYearCode IN ([2009], [2010], [2011])) AS pvt

ORDER BY vcEmployeeUniqueID

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

yogi123 SSCrazy Points: 2932 More actions · Answer 5

Eugene Elutin (11/13/2012)
BTW. Formatting your query nice, improves its readability and therefore its maintainability:
SELECT vcEmployeeUniqueID
,vcLastName
,vcFirstName
,[2009] AS SY2009
,[2010] AS SY2010
,[2011] AS SY2011
FROM #X1 AS src
PIVOT (MAX(vcSchoolName)
FOR iSchoolYearCode IN ([2009], [2010], [2011])) AS pvt
ORDER BY vcEmployeeUniqueID

Ohh Sweet !!!

great, How i forgot max in Pivoting

Thank You so much