Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Question

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Viewing 15 posts - 181 through 195 (of 243 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

sdznet (10/14/2009)
the content is very useful.
one question-
is it "somewhat" less complex to produce multi-aggregate crosstabs and pivots using ms-access than currently in sql server?

If knowledge about MS-Access were gasoline, I wouldn't have enough to run a sugar ant's mini-bike through a matchbox 😛 so I'd have to defer to someone more knowledgeable on the subject. But, IIRC, the answer is "Yes", MS-Access does make it quite a bit easier. That seemed to be the chief complaint when the PIVOT function came out in T-SQL... folks had hoped it would be as easy as MS-Access but it wasn't really even close.

That being said, supposedly Reporting Services' MATRIX does make it as easy as Access. I'm a bit of a hard-core data troll and I haven't used Reporting Services, either (yet).

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

sdznet Grasshopper Points: 10 More actions · Answer 2

that helps! just wanted to make sure i didn't miss an easier solution in sql server. as for access, i mostly use it as a quick prototype tool for proof of concept before building a real solution in sql (as in this case).

David Burrows SSC Guru Points: 65143 More actions · Answer 3

Outstanding 😀

Nice read too! :w00t:

Been using cross-tabs for longer than I care to remember :doze:

But a nice reminder is great, refreshes the spirit and cleanses the soul 😛

Far away is close at hand in the images of elsewhere.
Anon.

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

Thanks, David. Coming from someone with your background, that's a great compliment.

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

Thanks Jeff! I was looking into using Pivot instead of the Case method, and I came to the same end result. If you have multiple columns, the Case statement is cleaner code, and it performs better. The fact that you came to the same conclusion makes me a lot more confident.

Signature is NULL

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Thanks for the feedback, Calvin. Like you say, it's always good when someone comes to the same conclusion. 😀

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

roopesh.purohit SSC Rookie Points: 48 More actions · Answer 7

Hi Jeff/Everyone,

Great Article,

I have a cross tab reporting project in SSRS 2005, I need to convert the survey responses/answeres and questions text into cross tab format for Example :-

I have :-

Survey Type , SurveyName , SurveyCode , QuestionText , Answer/Response , comments these fileds now I need to convert it into :-

Survey Type , SurveyName , SurveyCode , Question 1 , Answer1 , Question 2 , Answer 2 ------Question 6, Answer 6 , Coments , Average By User.

Row Example Data:-

Surevey types are:- Normal , Professional, Enginner

Survey Names are:- Project , Code , Methods

Survey Codes are P1, C1, M1

QuestionsText are :- are you agree with approach? , Is the course useful for you? , are you agree with the code?

Answers like :- 6, 7, 8,9 (answers can be multiple from different user)

Can you please tell me the efficient way to do this by using your code you mentioned in this article?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Thanks for the feedback on the article.

My suggestion would be to start a new post on the forums for this question (it'll expose it to more people and I'm at work right now so can't get to it). Now, before you just jump in and copy the post above, a lot of folks (including me) on this forum are going to bug you for test data in a "readily consumable" format and table creation statements so they can test their solution (good for your cause) before they post an answer. Don't assume that you know how to do that. Read and heed the article at the first link in my signature line below.

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

sdznet Grasshopper Points: 10 More actions · Answer 9

thanks for idea to use a case solution that sounds like a good idea. 😎

i will review that for phase 2.

my first solution (V1=MakeItWork) in sql 2008-

1-ssis packages to load the data to sql.

2-custom sql sp (proc) that uses the sql PIVOT command to pivot 1 column.

3-current code uses joins to pivot more than one column

(thought someone said pivot can handle multiple columns- have not verified this)

this solution is not easy to maintain...

the end report is produced in ssrs, but the real work is in the sp.

Dan

xxxxxxxxxxxxx SSC Journeyman Points: 83 More actions · Answer 10

Just wanted to say: excellent, well-written article. Kudos.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

doug.bass (3/24/2010)
Just wanted to say: excellent, well-written article. Kudos.

Always a pleasure to get that kind of feedback. Thanks Doug.

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

fhljudyfong Newbie Points: 1 More actions · Answer 12

I have a problem with the cross-tab. My records in the table are the in out records of each staff. Here are sample data:

Staff IDDate Time

426/04/201009:36:00

426/04/201013:11:00

426/04/201014:49:00

426/04/201017:59:00

427/04/201009:37:00

427/04/201013:18:00

427/04/201014:22:00

427/04/201018:08:00

428/04/201009:40:00

428/04/201013:03:00

428/04/201014:47:00

428/04/201018:38:00

429/04/201009:36:00

429/04/201013:04:00

429/04/201013:58:00

429/04/201018:37:00

526/04/201008:47:00

526/04/201013:05:00

526/04/201013:38:00

526/04/201017:50:00

527/04/201008:53:00

527/04/201013:01:00

527/04/201013:36:00

527/04/201017:50:00

528/04/201008:57:00

528/04/201013:02:00

528/04/201013:03:00

528/04/201013:30:00

528/04/201017:46:00

529/04/201009:09:00

529/04/201013:03:00

529/04/201014:29:00

529/04/201018:02:00

529/04/201018:03:00

Would like to have the output :

Staff ID26/04/201027/04/201028/04/201029/04/2010

409:36:0009:37:0009:40:0009:36:00

413:11:0013:18:0013:03:0013:04:00

414:49:0014:22:0014:47:0013:58:00

417:59:0018:08:0018:38:0018:37:00

508:47:0008:53:0008:57:0009:09:00

513:05:0013:01:0013:02:0013:03:00

513:38:0013:36:0013:03:0014:29:00

517:50:0017:50:0013:30:0018:02:00

517:46:0018:03:00

Since each staff may have different number of in out records for each day and every day at least having 4 records. How can I make a cross-tab query?

Thanks in advance.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

fhljudyfong (5/4/2010)
I have a problem with the cross-tab. My records in the table are the in out records of each staff. Here are sample data:
Staff IDDate Time
426/04/201009:36:00
426/04/201013:11:00
426/04/201014:49:00
426/04/201017:59:00
427/04/201009:37:00
427/04/201013:18:00
427/04/201014:22:00
427/04/201018:08:00
428/04/201009:40:00
428/04/201013:03:00
428/04/201014:47:00
428/04/201018:38:00
429/04/201009:36:00
429/04/201013:04:00
429/04/201013:58:00
429/04/201018:37:00
526/04/201008:47:00
526/04/201013:05:00
526/04/201013:38:00
526/04/201017:50:00
527/04/201008:53:00
527/04/201013:01:00
527/04/201013:36:00
527/04/201017:50:00
528/04/201008:57:00
528/04/201013:02:00
528/04/201013:03:00
528/04/201013:30:00
528/04/201017:46:00
529/04/201009:09:00
529/04/201013:03:00
529/04/201014:29:00
529/04/201018:02:00
529/04/201018:03:00
Would like to have the output :
Staff ID26/04/201027/04/201028/04/201029/04/2010
409:36:0009:37:0009:40:0009:36:00
413:11:0013:18:0013:03:0013:04:00
414:49:0014:22:0014:47:0013:58:00
417:59:0018:08:0018:38:0018:37:00
508:47:0008:53:0008:57:0009:09:00
513:05:0013:01:0013:02:0013:03:00
513:38:0013:36:0013:03:0014:29:00
517:50:0017:50:0013:30:0018:02:00
517:46:0018:03:00
Since each staff may have different number of in out records for each day and every day at least having 4 records. How can I make a cross-tab query?
Thanks in advance.

Two things you need to be aware of, Judy. First, this particular thread will likely only be read by people who have subscribed to it (because it's an article thread) and many of them may not have the time to help you directly. You would be much better off posting it on one of the TSQL or General threads on this same site. I know I don't have the time to do a deep dive on your stuff this morning and wouldn't be able to get to it for at least the next 14 hours.

Second, thanks for the data but if you really want some very good help very quickly, the data needs to be in a "readily consumable" format. Please read the article at the first link in my signature line below for how to do that.

Thanks.

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

srweal Newbie Points: 1 More actions · Answer 14

You are a gun Jeff. Got this tasty article in my inbox and was so glad I stopped by for a read. PIVOT scared me off a few months back and I have been pondering how to get some answers out of my EAV tables.

This approach seems to get me on the way. Love it.

Btw, in the conclusion of your Part 1 you mention something in future parts about EAV/NVP. Is that going to be in a forthcoming article, or have I misinterpreted what you were getting at there?

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 15

Great to see this technique reviewed again. I came across it in Ken Henderson's book "The Guru's Guide to Transact-SQL" and have used it time and time again to normalise (and de-normalise 🙁 ) data structures.

As an aside (and without reading all 20 pages on this thread) I played around with the PIVOT operator on a SELECT statement and found some interesting performance metrics with large-ish datasets (100,000 rows).