March 27, 2008 at 12:42 am
Hi Folks,
My salesfact table consists sales operations and table strucure is like below:
CREATE TABLE [dbo].[SalesFact](
[dteIslemTarihi] [char](30),
[lCikisMiktar1] [decimal](14, 4) NOT NULL,
[lIadeMiktar1] [decimal](14, 4) NOT NULL,
[lCikisTutar] [decimal](18, 2) NOT NULL,
[lIadeTutar] [decimal](18, 2) NOT NULL,
[lIskontoTutari] [decimal](18, 2) NOT NULL,
[Organ_ID] [varchar](30),
[Line_ID] [char](10),
[Cins_ID] [varchar](7),
[Model_ID] [char](20)
) ON [PRIMARY]
My purpose is creating a crosstab report by selecting distinct lCikisiTutar column values.At the and of the table you will see a column named "Model_ID".I assume that a Model_ID can have maximum 5 distinct (lcikistutar) values.
So, myfinal resultset should like below:
Model_ID - (lcikistutar1)-(lcikistutar2)-(lcikistutar3)-(lcikistutar4)-(lcikistutar5)
That's it..
March 27, 2008 at 5:05 am
-- Stage the data
CREATE TABLE#Stage
(
RowID INT IDENTITY(1, 1),
Model_ID CHAR(20),
lCikisTutar DECIMAL(18, 2)
)
INSERT#Stage
(
Model_ID,
lCikisTutar
)
SELECTModel_ID,
lCikisTutar
FROMSalesFact
ORDER BYModel_ID,
lCikisTutar DESC
-- Show the expected output
SELECTs.Model_ID,
MAX(CASE WHEN s.RowID = x.minID + 0 THEN s.lCikisTutar ELSE 0.0 END) AS lCikisTutar1,
MAX(CASE WHEN s.RowID = x.minID + 1 THEN s.lCikisTutar ELSE 0.0 END) AS lCikisTutar2,
MAX(CASE WHEN s.RowID = x.minID + 2 THEN s.lCikisTutar ELSE 0.0 END) AS lCikisTutar3,
MAX(CASE WHEN s.RowID = x.minID + 3 THEN s.lCikisTutar ELSE 0.0 END) AS lCikisTutar4,
MAX(CASE WHEN s.RowID = x.minID + 4 THEN s.lCikisTutar ELSE 0.0 END) AS lCikisTutar5
FROM#Stage AS s
INNER JOIN(
SELECTModel_ID,
MIN(RowID) AS minID,
MIN(RowID) + 4 AS maxID
FROM#Stage
GROUP BYModel_ID
) AS x ON x.Model_ID = s.Model_ID
WHEREs.Row_ID BETWEEN x.minID AND x.maxID
ORDER BYs.Model_ID
N 56°04'39.16"
E 12°55'05.25"
March 27, 2008 at 5:38 am
Thanks for your reply..Bu there are still duplicate values on same rows 🙁
March 27, 2008 at 5:43 am
You can see your query result at the file you sent 🙁
I am not an SQL expert but I really wonder is this really so difficult case?
March 27, 2008 at 5:52 am
Throw in a DISTINCT !
SELECT DISTINCT Model_ID,
lCikisTutar
FROM SalesFact
ORDER BY Model_ID,
lCikisTutar DESC
You can also change the
ELSE 0.0 END
to
ELSE NULL END
if you don't want 0.0 to displayed for hose models not having 5 distinct lCikisTutar values.
Remember that we don't know anything about your system. This is the first time we hear about it.
You really should have the guts and knowledge to test the code and play around with it, yourself.
N 56°04'39.16"
E 12°55'05.25"
March 27, 2008 at 6:05 am
Yessssss...You are EXACTLY right...
It works like a machine..
Thank you very much for your PERFECT solution
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply