April 14, 2008 at 8:50 am
I have the following table
CREATE TABLE MyTable
(
MyID VARCHAR(50) NOT NULL,
Type VARCHAR(50) NOT NULL,
MyDESCRIPTION VARCHAR(80) NOT NULL,
Ref VARCHAR(50),
MyAMOUNT INT,
TAXAMOUNT1 INT,
TAXAMOUNT2 INT
)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type1','Type1','ABC1',20,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type1','Type1','ABC2',22,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type1','Type1','ABC3',30,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type2','Type2','123_1',25,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type2','Type2','123_2',45,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type2','Type2','123_3',35,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type3','Type3','',0,1,2)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type3','Type3','',0,5,6)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('1','Type3','Type3','',0,8,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type1','Type1','DEF1',40,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type1','Type1','DEF2',42,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type1','Type1','DEF3',40,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type2','Type2','456_1',65,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type2','Type2','456_2',75,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type2','Type2','456_3',15,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type2','Type2','456_4',95,0,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type3','Type3','',0,10,0)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type3','Type3','',0,13,12)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type3','Type3','',0,0,50)
INSERT INTO MyTable (MyID,Type,MyDESCRIPTION,Ref,MyAMOUNT,TAXAMOUNT1,TAXAMOUNT2)
VALUES ('2','Type3','Type3','',0,3,0)
And I need the following report with one statement, would please help me?
MyIDRefTyp1_MyAmountType2_MyAmountType3_TAXAMOUNT1Type3_TAXAmount2
1ABC1202512
1ABC2224556
1ABC3303580
2DEF14065100
2DEF242751312
2DEF34015050
29530
April 14, 2008 at 2:38 pm
I can put a query together to produce this report, but the relationships of the data to produce this report looks very weak or even technically not related. I think you need to give a bit of explanation of this data and how different pieces of the data relate to each other. To me it looks like it relates based on the order in which the data was inserted into the table. That might not be a sufficient technical basis to provide consistent predictable results.
April 14, 2008 at 2:53 pm
Unfortunatly there is no other way, yes I have to go by order.
Thanks for your help.
April 14, 2008 at 2:58 pm
Oh well, are you using SQL Server 2000 or SQL Server 2005?
April 14, 2008 at 4:19 pm
SQL Server 2000.
April 14, 2008 at 4:42 pm
You need to really understand what Kent said. In a relational database, the order in which rows are returned can't be guaranteed unless they are explicitly ordered using an 'ORDER BY' clause. You may get these rows returned in entry order for the first 500 executions of the query and then on the 501st execution, the rows come back differently (because of some internal reuse of space by the server), and your process will fail or produce incorrect results.
Is it possible to add something to the rows that will allow them to be sequenced in the desired order?
April 14, 2008 at 5:23 pm
Yep - you need something to identify the order. An identity field will do it, for example...
-- Data
CREATE TABLE MyTable (
ident int identity(1, 1), --<----- you need this
MyID VARCHAR(50) NOT NULL,
Type VARCHAR(50) NOT NULL,
MyDESCRIPTION VARCHAR(80) NOT NULL,
Ref VARCHAR(50),
MyAMOUNT INT,
TAXAMOUNT1 INT,
TAXAMOUNT2 INT)
INSERT MyTable
SELECT '1','Type1','Type1','ABC1',20,0,0
UNION ALL SELECT '1','Type1','Type1','ABC2',22,0,0
UNION ALL SELECT '1','Type1','Type1','ABC3',30,0,0
UNION ALL SELECT '1','Type2','Type2','123_1',25,0,0
UNION ALL SELECT '1','Type2','Type2','123_2',45,0,0
UNION ALL SELECT '1','Type2','Type2','123_3',35,0,0
UNION ALL SELECT '1','Type3','Type3','',0,1,2
UNION ALL SELECT '1','Type3','Type3','',0,5,6
UNION ALL SELECT '1','Type3','Type3','',0,8,0
UNION ALL SELECT '2','Type1','Type1','DEF1',40,0,0
UNION ALL SELECT '2','Type1','Type1','DEF2',42,0,0
UNION ALL SELECT '2','Type1','Type1','DEF3',40,0,0
UNION ALL SELECT '2','Type2','Type2','456_1',65,0,0
UNION ALL SELECT '2','Type2','Type2','456_2',75,0,0
UNION ALL SELECT '2','Type2','Type2','456_3',15,0,0
UNION ALL SELECT '2','Type2','Type2','456_4',95,0,0
UNION ALL SELECT '2','Type3','Type3','',0,10,0
UNION ALL SELECT '2','Type3','Type3','',0,13,12
UNION ALL SELECT '2','Type3','Type3','',0,0,50
UNION ALL SELECT '2','Type3','Type3','',0,3,0
-- Calculation
select
max(case when Type = 'Type1' then Ref else null end) as Ref,
max(case when Type = 'Type1' then MyAmount else null end) as Type1_MyAmount,
max(case when Type = 'Type2' then MyAmount else null end) as Type2_MyAmount,
max(case when Type = 'Type3' then TAXAMOUNT1 else null end) as Type3_TAXAMOUNT1,
max(case when Type = 'Type3' then TAXAMOUNT2 else null end) as Type3_TAXAMOUNT2
from (
select *, (select count(*) from MyTable where MyId = a.MyId and Type = a.Type and ident <= a.ident) x from MyTable a) a
group by x, MyID
/* Results
Ref Type1_MyAmount Type2_MyAmount Type3_TAXAMOUNT1 Type3_TAXAMOUNT2
---------- -------------- -------------- ---------------- ----------------
ABC1 20 25 1 2
ABC2 22 45 5 6
ABC3 30 35 8 0
DEF1 40 65 10 0
DEF2 42 75 13 12
DEF3 40 15 0 50
NULL NULL 95 3 0
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 14, 2008 at 8:03 pm
You do realize that your table is a bit denormalized? Can't you turn it into a true EAV (Entity, Attribute, Value) table?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply