sql server programming: I have few questions, and thanks to all of you!

I try to do pivot tables in few way, as long as one way works, it will
be great!

1. I have created pivottable query with Access adp to SQL server. it
shows out correctly as what I want. how can I export it? I have hard
to time export it as orginal format as I want, it always changes to
row format when I export it.
2. I try to run query with pivot() keyword but it doens't return as I
want. Please see example below.
3. any other way to give me what I want?

thank you!



I have used following but I didn't get what I want. thanks

(
aggregate_function ( value_column )
FOR pivot_column
IN ( <column_list> )
)



select * from tablename
PIVOT (
MAX(labOrganism)
FOR FK_Lab IN (A, B)
) AS PVt



ORIGINAL TABLE:
Pksex SexName Cat CatWord
F Female A F
F Female B fEM
M Male A M
M Male B MAL
M Male B MA


RETURN I WANT:
Pksex SexName A B
F Female F fEM
M Male M MAL
M Male MA




however, with "pivot", I must use "min" or "max" because it only shows
one line records. how can I make it to show all.. the returns I got
after using pivot keyword which shows as below
RETURN I GOT:
Pksex SexName A B
F Female F fEM
M Male M MAL

(bobyang03@gmail.com) writes:
[quoted text, click to view]

This should work:

SELECT PKSex, SexName,
A = MAX(CASE Cat WHEN 'A' THEN CatWord END),
B = MAX(CASE Cat WHEN 'B' THEN CatWord END)
FROM tbl
GROUP BY PKSex, SexName

You may note that I don't use the PIVOT operator at all. I have only
found this confusing - or am I just lazy to learn new syntax when I
have old syntax that does the job just as well.

The trick here to use MAX or MIN (which does not matter) to gather
what else would be different rows on the same row.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at