Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
-
Jeff Moden (12/3/2008)
jcraddock (12/3/2008)
I've had better luck writing code that writes my SQL. I wrote a set of code that you point at any table, it accepts any column within the table as the top or left portion of the crosstab and any numeric column as the data portion. Another option is to calculate as percent of the whole or sum or count.Doing it that way, you have one set of reusable code for all crosstabs...works pretty well. I usually just create a simple view, point the object at the view and Voila have instant reconfigurable, groupable by anything crosstab. I even added an option to subgroup on the left side.
Same concept, more code up front, but infinitely reusable.
I absolutely agree with that, Jim. I was going to try to write some code to do such a thing as you describe, but you beat me to it... probably by years.
I think folks would give up an eye-tooth to see such code. You haven't written an article since February of 2005... maybe it's time to put pen to paper again. It sounds like the "Rosetta Stone" for all dynamic cross-tabs and I know that I'd seriously like to see that code in an article...
I think I'm out of eye-teeth already, but motion seconded. All in favor?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
Hi Jeff,
I have a table that has the following values.
col1 | col2 | col3 | col4
1 | val1| val2 | val3
2 | val21| val22 | val23
3 | val31| val32 | val33
what I want is
for each col1 entries, I should have the rcol2,col3 and col4 values as
col2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3
val1 |val2 |val3 |val21 |val22 | val23 | val31 | val32 |val33
Could you please tell me how to do this?
Thanks for your help.
-Maria
-
psmg01 (12/17/2008)
Hi Jeff,I have a table that has the following values.
col1 | col2 | col3 | col4
1 | val1| val2 | val3
2 | val21| val22 | val23
3 | val31| val32 | val33
what I want is
for each col1 entries, I should have the rcol2,col3 and col4 values as
col2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3
val1 |val2 |val3 |val21 |val22 | val23 | val31 | val32 |val33
Could you please tell me how to do this?
Thanks for your help.
-Maria
Hi Maria,
First, I'd post this in one of the T-SQL forums on this site instead of as part of an article discussion... it'll get more attention than just from me that way.
Second, there's some missing information... for example, does the table only have 3 rows or are there more? If more, how do you want those handled. Is column 1 always going to be an unbroken sequence of integers?
With that in mind, we likely need more rows of data to give you a nice, tested example that actually works. Do to that, please read the article in the link contained in my signature below... a CREATE TABLE statement and usable INSERT statements go a long way to getting this type of question answered very quickly and accurately.
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) -
Hi Jeff,
Thank you for your reply.
I will post this in T_SQL forum too.
I am having a table like below
[CREATE TABLE [dbo].[Output_Info](
[Common_Id] [int] NOT NULL,
[Signal] [real] NOT NULL,
[Detect] [nchar](10) NOT NULL,
[PValue] [real] NOT NULL,
[EAID] [int] NOT NULL,
)]
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',42.5,22)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',32.5,23)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,32.3,'P',43.5,22)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,8.3,'P',2.5,23)
For each common_Id, I would like to get the rows as
common_Id Signal_EAID,Detect_EAID,PValue_EAID
sO THE result column headers should look like this
commonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23
112.3p42.5 12.3 P 32.5
2 32.3 P 43.5 8.3 P 2.5
Is it possible to do this?
I am confused about this and got stuck here.
Please help me.
Thanks,
-Maria
-
jcrawf02 (12/11/2008)
Jeff Moden (12/3/2008)
jcraddock (12/3/2008)
I've had better luck writing code that writes my SQL. I wrote a set of code that you point at any table, it accepts any column within the table as the top or left portion of the crosstab and any numeric column as the data portion. Another option is to calculate as percent of the whole or sum or count.Doing it that way, you have one set of reusable code for all crosstabs...works pretty well. I usually just create a simple view, point the object at the view and Voila have instant reconfigurable, groupable by anything crosstab. I even added an option to subgroup on the left side.
Same concept, more code up front, but infinitely reusable.
I absolutely agree with that, Jim. I was going to try to write some code to do such a thing as you describe, but you beat me to it... probably by years.
I think folks would give up an eye-tooth to see such code. You haven't written an article since February of 2005... maybe it's time to put pen to paper again. It sounds like the "Rosetta Stone" for all dynamic cross-tabs and I know that I'd seriously like to see that code in an article...
I think I'm out of eye-teeth already, but motion seconded. All in favor?
Ouch, I wrote a large reply and my session timed out.
Here is the abbreviated reply. I couldn't show how to do this in just one article because the code is mostly in ASP.NET. This is because it is written on the foundation of the metadata based/data driven model I discussed in my original article. However, you could do the same thing mostly in a stored procedure, but the front-end would still need coded in something.
Here are some screenshots of the crosstab portion of my application
This is a simple output of a query (the query is shown at the top for me as a debug tool, other users don't see it.)
This is page I use to record the meta data about the report.
This is where I record which columns can be filtered on.
And here is an example of a complex query it automagically generates on a more complex view.
All that is required for a new crosstab is to load the metadata seen in shots two and three.
-
jcraddock (12/17/2008)
Ouch, I wrote a large reply and my session timed out.Here is the abbreviated reply. I couldn't show how to do this in just one article... (snip)
I learned the hard way too. After losing many a potential post to timeout, and getting burned and not re-doing all the post, I started doing a quick Ctrl+C (copy) of the post before sending. The reply form times out on me all the time, because I tool with it, get pulled in another direction, and come back, and poof, timeout and loss of my text. Since snagging a copy already has the Quoted section in it, you don't have to go find the response you were quoting, just reply to the article again, and paste.
I would be interested in your code... (grin). Is it in old ASP or ASP.NET?
-
Hi SSC,
Thanks for your reply.
Is there a url for a site, where I can generate sql for crosstab?
or How to write the query in my case?
I am sorry, I don't understand where to start.
I am very new to sqlserver.
Please help me
Thanks,
Maria
-
dphillips (12/17/2008)
I learned the hard way too. After losing many a potential post to timeout, and getting burned and not re-doing all the post, I started doing a quick Ctrl+C (copy) of the post before sending. The reply form times out on me all the time, because I tool with it, get pulled in another direction, and come back, and poof, timeout and loss of my text. Since snagging a copy already has the Quoted section in it, you don't have to go find the response you were quoting, just reply to the article again, and paste.
I would be interested in your code... (grin). Is it in old ASP or ASP.NET?
It was originally written in ASP, but we re-wrote in in ASP.NET several years ago. We found going to c# to be very hours intensive, so we settled for vb.net.
I cannot share the code, as I co-own it with the University. I have thought about writing more on the metadata driven approach to web development. I honestly cannot believe there isn't a good source on how to do it anywhere. There is a commercial system available that offers the same basic approach. They do their front-end a bit differently. I actually thought about adopting it, but found it didn't quite meet all my needs.
-
psmg01 (12/17/2008)
Hi SSC,Thanks for your reply.
Is there a url for a site, where I can generate sql for crosstab?
or How to write the query in my case?
I am sorry, I don't understand where to start.
I am very new to sqlserver.
Please help me
Thanks,
Maria
Yes... the article which is the source of this thread provides an introduction... 😉
--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) -
jcraddock (12/17/2008)
It was originally written in ASP, but we re-wrote in in ASP.NET several years ago. We found going to c# to be very hours intensive, so we settled for vb.net.
I cannot share the code, as I co-own it with the University.
So just post your half:
SEL col, umn2, olumn3
FR able
ERE fiel = ield2
😛
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
psmg01 (12/17/2008)
Hi Jeff,I have a table that has the following values.
col1 | col2 | col3 | col4
1 | val1| val2 | val3
2 | val21| val22 | val23
3 | val31| val32 | val33
what I want is
for each col1 entries, I should have the rcol2,col3 and col4 values as
col2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3
val1 |val2 |val3 |val21 |val22 | val23 | val31 | val32 |val33
Could you please tell me how to do this?
Thanks for your help.
-Maria
Hello Maria,
Here's the general approach to this problem using the RAC utility. The idea is to first create a crosstab and then rotate it to get the kind of row you want.
create table R1 (col1 int primary key, col2 char(4), col3 char(4), col4 char(4))
insert R1 values(1, 'val11', 'val12', 'val13')
insert R1 values(2, 'val21', 'val22', 'val23')
insert R1 values(3, 'val31', 'val32', 'val33')
Exec Rac
@transform='Max(col2) as col2 & Max(col3) as col3 & Max(col4) as col4',
@rows='rowid',
@pvtcol='col1',
@from='(select 1 as rowid,col1,col2,col3,col4
from R1) as A',
@row_totals='n',@grand_totals='n',@rowbreak='n',
@rotate='nest',@shell='no',@racheck='y',
@select='select _pvtcols_ from rac'
col2_1 col3_1 col4_1 col2_2 col3_2 col4_2 col2_3 col3_3 col4_3
------ ------ ------ ------ ------ ------ ------ ------ ------
val11 val12 val13 val21 val22 val23 val31 val32 val33
Note the default RAC rotated column names. Great minds must think alike:)
Visit RAC @
-
psmg01 (12/17/2008)
Hi Jeff,Thank you for your reply.
I will post this in T_SQL forum too.
I am having a table like below
[CREATE TABLE [dbo].[Output_Info](
[Common_Id] [int] NOT NULL,
[Signal] [real] NOT NULL,
[Detect] [nchar](10) NOT NULL,
[PValue] [real] NOT NULL,
[EAID] [int] NOT NULL,
)]
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',42.5,22)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',32.5,23)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,32.3,'P',43.5,22)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,8.3,'P',2.5,23)
For each common_Id, I would like to get the rows as
common_Id Signal_EAID,Detect_EAID,PValue_EAID
sO THE result column headers should look like this
commonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23
112.3p42.5 12.3 P 32.5
2 32.3 P 43.5 8.3 P 2.5
Is it possible to do this?
I am confused about this and got stuck here.
Please help me.
Thanks,
-Maria
Thanks for making it eay to use your data... but we still need to know... are there only going to be two matching rows or not? If there are more than 2, what do you want to do?
Again, I wouldn't post here... I would post it on the new thread you started.
--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) -
Jeff,
It will more than 2 rows, They are dynamic and it will change based on each experiment.
In that case, I have to display them as follows.
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',42.5,22)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',32.5,23)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,32.3,'P',43.5,22)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,8.3,'P',2.5,23)
--------------
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',42.5,24)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(1,12.3,'P',32.5,25)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,32.3,'P',43.5,24)
INSERT INTO [dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])
VALUES
(2,8.3,'P',2.5,25)
commonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23 Signal_24 Detect_24 PValue_24 Signal_25 Detect_25 PValue_25
1 12.3 p 42.5 12.3 P 32.5 12.3 p 42.5 12.3 P 32.5
2 32.3 P 43.5 8.3 P 2.5 32.3 P 43.5 8.3 P 2.5
Thanks,
Maria
PS
I will copy and paste all output in TSQL forum too.
-
Although I know the value this article can represent you can actually be far more generic than you suggest.
You can write an SP that given the Source Table or View, the Key Columns, and the "value" column and Aggregate can produce the Cross tab query (and execute it)....
This is a method (query) I have been using for several years to do cross Tabs. It is Also why I dislike the the MS version of Pivot as you have to know what is in the column you are pivoting
-
AnzioBake (12/22/2008)
Although I know the value this article can represent you can actually be far more generic than you suggest.You can write an SP that given the Source Table or View, the Key Columns, and the "value" column and Aggregate can produce the Cross tab query (and execute it)....
This is a method (query) I have been using for several years to do cross Tabs. It is Also why I dislike the the MS version of Pivot as you have to know what is in the column you are pivoting
Kewl! Got some code you'd like to share?
--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)
Viewing 15 posts - 61 through 75 (of 130 total)
You must be logged in to reply to this topic. Login to reply