convert pivot to simple select statement

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

SQL Server 2005 Forums

Transact-SQL (2005)

convert pivot to simple select statement

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-06-08 : 14:46:09

hi every body
i want to convert a pivot statement to a simple select statment, and i want it to use in a access database!!
in a project i have to work with access, help me on this:

i have a table named tasadof
tasadof [ tasadof_id (long integer), estekhdam (text) ]

here is the sample data:

1 vazife
2 kadr
3 kadr
4 kadr

my query is:

select  [vazife],[kadr] from
( select tasadof_id,estekhdam from tasadof) p
pivot 
(
count(tasadof_id)
for estekhdam in
( [vazife],[kadr]  )
)as pvt

the output must be:
vazif kadr
1 3
how can i convert it to use in access?
thanks

****<< I Love MTN.SH >>****

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 15:06:35

Use the TRANSFORM keyword.

N 56°04'39.26"
E 12°55'05.63"

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-06-08 : 15:17:36

thanks
i don't know how to use Transform, Can u tell Me details

****<< I Love MTN.SH >>****

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 15:59:07

Read the Microsoft Access help file.

N 56°04'39.26"
E 12°55'05.63"

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-06-10 : 02:16:01

hi again
i read the ms access help and i did it,and it works.
thanks
but the way access do this job is a creating a cross table, and the result have more than one row.and i should sum the value in the same columns for all rows to get the final result
is there any way to have only one row as a result?

****<< I Love MTN.SH >>****

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources