Making it pivot

Question

Post reply

Making it pivot

ccs9056

Newbie

Points: 2
More actions
June 10, 2008 at 10:53 pm

#187861

Hello. I am trying to make a simple pivot table using SQL. I am new to this so do not fully understand the complex codes I find when I search. I am using sql server 2000. My query is.
SELECT data1, data2, data3, data4, sum(data5)
from database1
where data6 like 'dosentmatter%'
group by data1, data2, data3, data4
order by 2, 1
I am trying to make it so that you have
data 1,2 and 3 as a row headings
data 4 as the column heading
data 5 as the column and row intersections
Very easy to do in Access… Not SQL (for me)
Any tips would be greatly appreciated.
Thank you.

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI,

If you do a search for SQL Cross Tab queries you should find loads of examples.

They will involve CASE statements

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

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

SQL Server 2005 allows you to write PIVOT queries. Lookup the keyword PIVOT in Books Online. Books Online comes free with SQL Server and is the "help" system for it. Study that first and if you still don't get it, c'mon back.

--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)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

True about the pivot, but even though the thread is under 2005 I think the guy is using 2000

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

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

You're right... I missed where the OP said that. Thanks, Christopher. And, yes... lookup Cross Tab Reports...

--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)

ccs9056 Newbie Points: 2 More actions · Answer 5

thanks anyway. hopefully i wont get banned for saying this but just reverted the job back to access... have a deadline so... can make it better later.

steve dassin SSCrazy Points: 2396 More actions · Answer 6

Check out RAC. It's similar to Access crosstab query but much more powerful with many options. If you like Access you'll appreciate RAC. Especially if you don't want to code lovely sql 🙂

www.rac4sql.net

www.beyondsql.blogspot.com