Report
-
I have a table like
Mydate
COL1
COL2
COL3
4/1/2004
2.3
7.6
10
4/2/2004
7
4
2
4/3/2004
1
5
2
4/4/2004
4
8
4
And i need a report
4/1/2004
4/2/2004
4/3/2004
4/4/2004
COL1
2.3
7
1
4
COL2
7.6
4
5
8
COL3
10
2
2
4
Is there any way to get these report with sql statement, please help me.
-
There is some info on pivot tables in BOL.
Yukon will support pivot tables natively in the syntax but I think SS2K would need a stepped approach
cheers
dbgeezer
-
select
max(decode(mydate,to_date('01/04/2004','dd/mm/yyyy'),col1,0))"4/1/2004",
max
(decode(mydate,to_date(
'02/04/2004','dd/mm/yyyy'),col1,0))"4/2/2004",
max
(decode(mydate,to_date(
'03/04/2004','dd/mm/yyyy'),col1,0))"4/3/2004",
max
(decode(mydate,to_date(
'01/04/2004','dd/mm/yyyy'),col1,0))"4/4/2004"
from
reportdata
union
select
max(decode(mydate,to_date(
'01/04/2004','dd/mm/yyyy'),col2,0))"4/1/2004",
max
(decode(mydate,to_date(
'02/04/2004','dd/mm/yyyy'),col2,0))"4/2/2004",
max
(decode(mydate,to_date(
'03/04/2004','dd/mm/yyyy'),col2,0))"4/3/2004",
max
(decode(mydate,to_date(
'01/04/2004','dd/mm/yyyy'),col2,0))"4/4/2004"
from
reportdata
union
select
max(decode(mydate,to_date(
'01/04/2004','dd/mm/yyyy'),col3,0))"4/1/2004",
max
(decode(mydate,to_date(
'02/04/2004','dd/mm/yyyy'),col3,0))"4/2/2004",
max
(decode(mydate,to_date(
'03/04/2004','dd/mm/yyyy'),col3,0))"4/3/2004",
max
(decode(mydate,to_date(
'04/04/2004','dd/mm/yyyy'),col3,0))"4/4/2004"
from
reportdata
------Pls check this one i think this one will solve urs problem
-
Usually when you have this problem, it means that the design of your table needs to be further normalized. If you were to refactor the table to be like
ID Type Date Value
1 Col1 4/1/2004 2.3
2 Col2 4/1/2004 7.6
3 Col3 4/1/2004 10
4 Col1 4/2/2004 7
…
Then you will be able to cross tab the information in either direction. You will probably need to create a separate lookup table for the Type to properly normalize it.
This is especially helpfull when you encounter a table that has many columns of similar data. I actually wrote a program many years ago that I called "Un-crosstab" which automatically extracted such a table in to refactored temp tables and then ran the report cross tabbed the other direction. I have reused that program many times over the years.
Dave C>
-
BABITA,
I think your code is for Oracle.
Dave,
Unfortunatly i cannot change the table structure, you mentioned that you had a program for this, could you please tell me hints how can i code that type of program.
Thanks
-
-- create a stored procedure which:
-- reads & normalizes your data
-- by (create table date, name, value)
-- then reads it back in matrix form
-- so you can manipulate the data
-- anyway you want (NORMALIZED!)
DECLARE
@COL1 integer,
@COL2 integer,
@COL3 integer,
@MYDATE Nvarchar(100),
@MYTYPE Nvarchar(100),
@MYVALUE Nvarchar(100)
create table gridTable(
ID integer IDENTITY (1, 1) NOT NULL,
MYDATE Nvarchar(100),
MYTYPE Nvarchar(100),
MYVALUE Nvarchar(100)
)
CREATE INDEX MYINDEX_1 ON gridTable(MYDATE);
CREATE INDEX MYINDEX_2 ON gridTable(MYTYPE,MYDATE);
DECLARE GridColumns INSENSITIVE CURSOR FOR
SELECT MYDATE, COL1, COL2, COL3
FROM MYTABLE
OPEN GridColumns
WHILE (1=1)
BEGIN
-- PROCESS to get all records into new table
FETCH GridColumns INTO @MYDATE, @COL1, @COL2, @COL3
-- break or continue fetching
IF (@@FETCH_STATUS=-1) BREAK -- Exit end of loop
IF (@@FETCH_STATUS=-2) CONTINUE -- Next item
-- START OF NORMALIZATION PROCESS
-- normalize column 1
INSERT INTO gridTable(MYDATE,MYTYPE,MYVALUE)
VALUES(@MYDATE,'COL1',CONVERT(varchar(11),@COL1))
-- normalize column 2
INSERT INTO gridTable(MYDATE,MYTYPE,MYVALUE)
VALUES(@MYDATE,'COL2',CONVERT(varchar(11),@COL2))
-- normalize column 2
INSERT INTO gridTable(MYDATE,MYTYPE,MYVALUE)
VALUES(@MYDATE,'COL3',CONVERT(varchar(11),@COL3))
END
CLOSE GridColumns
DEALLOCATE GridColumns
-- NOW CODE THIS PART YOURSELF, PLEASE!!
-- FIRST READ of new table called gridTable
-- each row has (one date) & (one value)
-- read distinct MYDATE & use value [MYDATE]
-- as @MYDATE_01, @MYDATE_02, @MYDATE_03
-- for your column headings & variables for
-- testing if MYDATE lookup matches MYDATE
-- SECOND READ same table now for all values
-- read table (ORDER BY MYTYPE, MYDATE)
-- read each record & build your cross table
-- COLUMN-DATE(value),COLUMN-DATE(value),
-- COLUMN-DATE(value), etc...
REMEMBER TO NORMALIZE YOUR DATA (IN TABLES)
Regards,Coach James
-
Thank you!, it worked.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply