October 14, 2009 at 9:06 am
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
October 14, 2009 at 9:30 am
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).
October 22, 2009 at 6:49 am
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.
October 22, 2009 at 6:56 pm
Thanks, David. Coming from someone with your background, that's a great compliment.
--Jeff Moden
December 15, 2009 at 3:24 pm
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
December 15, 2009 at 4:28 pm
Thanks for the feedback, Calvin. Like you say, it's always good when someone comes to the same conclusion. 😀
--Jeff Moden
December 30, 2009 at 3:25 am
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?
December 30, 2009 at 7:50 am
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
December 30, 2009 at 8:48 am
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
March 24, 2010 at 2:29 pm
Just wanted to say: excellent, well-written article. Kudos.
March 24, 2010 at 4:49 pm
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
May 4, 2010 at 2:55 am
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 ID Date Time
4 26/04/2010 09:36:00
4 26/04/2010 13:11:00
4 26/04/2010 14:49:00
4 26/04/2010 17:59:00
4 27/04/2010 09:37:00
4 27/04/2010 13:18:00
4 27/04/2010 14:22:00
4 27/04/2010 18:08:00
4 28/04/2010 09:40:00
4 28/04/2010 13:03:00
4 28/04/2010 14:47:00
4 28/04/2010 18:38:00
4 29/04/2010 09:36:00
4 29/04/2010 13:04:00
4 29/04/2010 13:58:00
4 29/04/2010 18:37:00
5 26/04/2010 08:47:00
5 26/04/2010 13:05:00
5 26/04/2010 13:38:00
5 26/04/2010 17:50:00
5 27/04/2010 08:53:00
5 27/04/2010 13:01:00
5 27/04/2010 13:36:00
5 27/04/2010 17:50:00
5 28/04/2010 08:57:00
5 28/04/2010 13:02:00
5 28/04/2010 13:03:00
5 28/04/2010 13:30:00
5 28/04/2010 17:46:00
5 29/04/2010 09:09:00
5 29/04/2010 13:03:00
5 29/04/2010 14:29:00
5 29/04/2010 18:02:00
5 29/04/2010 18:03:00
Would like to have the output :
Staff ID 26/04/2010 27/04/2010 28/04/2010 29/04/2010
4 09:36:00 09:37:00 09:40:00 09:36:00
4 13:11:00 13:18:00 13:03:00 13:04:00
4 14:49:00 14:22:00 14:47:00 13:58:00
4 17:59:00 18:08:00 18:38:00 18:37:00
5 08:47:00 08:53:00 08:57:00 09:09:00
5 13:05:00 13:01:00 13:02:00 13:03:00
5 13:38:00 13:36:00 13:03:00 14:29:00
5 17:50:00 17:50:00 13:30:00 18:02:00
5 17:46:00 18: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.
May 4, 2010 at 5:04 am
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 ID Date Time
4 26/04/2010 09:36:00
4 26/04/2010 13:11:00
4 26/04/2010 14:49:00
4 26/04/2010 17:59:00
4 27/04/2010 09:37:00
4 27/04/2010 13:18:00
4 27/04/2010 14:22:00
4 27/04/2010 18:08:00
4 28/04/2010 09:40:00
4 28/04/2010 13:03:00
4 28/04/2010 14:47:00
4 28/04/2010 18:38:00
4 29/04/2010 09:36:00
4 29/04/2010 13:04:00
4 29/04/2010 13:58:00
4 29/04/2010 18:37:00
5 26/04/2010 08:47:00
5 26/04/2010 13:05:00
5 26/04/2010 13:38:00
5 26/04/2010 17:50:00
5 27/04/2010 08:53:00
5 27/04/2010 13:01:00
5 27/04/2010 13:36:00
5 27/04/2010 17:50:00
5 28/04/2010 08:57:00
5 28/04/2010 13:02:00
5 28/04/2010 13:03:00
5 28/04/2010 13:30:00
5 28/04/2010 17:46:00
5 29/04/2010 09:09:00
5 29/04/2010 13:03:00
5 29/04/2010 14:29:00
5 29/04/2010 18:02:00
5 29/04/2010 18:03:00
Would like to have the output :
Staff ID 26/04/2010 27/04/2010 28/04/2010 29/04/2010
4 09:36:00 09:37:00 09:40:00 09:36:00
4 13:11:00 13:18:00 13:03:00 13:04:00
4 14:49:00 14:22:00 14:47:00 13:58:00
4 17:59:00 18:08:00 18:38:00 18:37:00
5 08:47:00 08:53:00 08:57:00 09:09:00
5 13:05:00 13:01:00 13:02:00 13:03:00
5 13:38:00 13:36:00 13:03:00 14:29:00
5 17:50:00 17:50:00 13:30:00 18:02:00
5 17:46:00 18: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
May 6, 2010 at 6:57 am
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?
August 6, 2010 at 1:36 am
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).
Viewing 15 posts - 181 through 195 (of 243 total)
You must be logged in to reply to this topic. Login to reply