CROSS TAB Query Question.

Question

CROSS TAB Query Question.

keyun

SSCrazy

Points: 2397
More actions
April 23, 2010 at 2:54 pm

#140499

I read bunch of post here but couldnt able to find out and could not able to come up with the result i want.
Here is the data i have
Account Prod_CodeAmount
F100810039136415 218.90
F100810039136415 401.90
F100810039137210 218.90
F100810039137210 401.90
F100810039180048 218.90
F100810039180048 401.90
F100810039181025 218.90
F100810039181025 401.90
Now what I would like to see is
Account Prod_CodeAmount_1 Amount_2
F100810039136415 218.90 401.90
F100810039137210 218.90 401.90
F100810039180048 218.90 401.90
F100810039181025 218.90 401.90
I have read lot of post but they are with Aggregate function but for me i do not need that functionality here the only thing i need is my Amount column need to splite in two by product_code. Since product has regular valude and markdown value.
Is this possbile?
Please advice,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Assuming Amount_1 and Amount_2 will always be in a consistent order (ege. being able to sort it by and ID column which is unfortunately not present (or at least not mentioned) you could use a subquery or CTE together with: ROW_NUMBER() OVER (PARTITION BY Account, Prod_Code ORDER BY ID)

Based on that you could use the "standard" CrossTab. Don't get confused by the aggregation used within the CrossTab - you'll need it as well. The aggregation is used to eliminate the values from the CASE statement that you don't want to display.

There are two ways to figure it out: follow the related link in my signature and modify the sample to meet your requirement or post some ready to use sample data as described in the first link in my signature to get a coded and tested answer.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 2

As Imu92 said, you don't give us a lot of details about how the data will look, but assuming it is all consistent with your sample and there are only two price/amount records per account/Prod_Code...

How about a self join?

SELECT P1.Account, P1.Prod_Code, P1.Amount, P2.Amount

FROM ProdTable P1 JOIN ProdTable P2

on P1.Account=P2.Account

AND P1.Prod_Code = P2.Prod_Code

AND P1.Amount < P2.Amount

Rob Schripsema
Propack, Inc.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Rob Schripsema (4/23/2010)
As Imu92 said, you don't give us a lot of details about how the data will look, but assuming it is all consistent with your sample and there are only two price/amount records per account/Prod_Code...
How about a self join?
SELECT P1.Account, P1.Prod_Code, P1.Amount, P2.Amount
FROM ProdTable P1 JOIN ProdTable P2
on P1.Account=P2.Account
AND P1.Prod_Code = P2.Prod_Code
AND P1.Amount < P2.Amount

If you don't care about performance at all, yes, it might be an option 😉

And what will the code look like if there is a need to have 5 or 6 codes? :w00t:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

lmu92 (4/23/2010)
Rob Schripsema (4/23/2010)
As Imu92 said, you don't give us a lot of details about how the data will look, but assuming it is all consistent with your sample and there are only two price/amount records per account/Prod_Code...
How about a self join?
SELECT P1.Account, P1.Prod_Code, P1.Amount, P2.Amount
FROM ProdTable P1 JOIN ProdTable P2
on P1.Account=P2.Account
AND P1.Prod_Code = P2.Prod_Code
AND P1.Amount < P2.Amount
If you don't care about performance at all, yes, it might be an option 😉
And what will the code look like if there is a need to have 5 or 6 codes? :w00t:

Exactly - and that's why I preceded my suggestion with the caveat that the data would only look like what he gave in the example. For THAT data structure, and a small number of records (two records per product code), I'd doubt performance would be an issue. And the self-join was a relatively simple way to solve the problem.

Rob Schripsema
Propack, Inc.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

keyun (4/23/2010)
I read bunch of post here but couldnt able to find out and could not able to come up with the result i want.
Here is the data i have
Account Prod_CodeAmount
F100810039136415 218.90
F100810039136415 401.90
F100810039137210 218.90
F100810039137210 401.90
F100810039180048 218.90
F100810039180048 401.90
F100810039181025 218.90
F100810039181025 401.90
Now what I would like to see is
Account Prod_CodeAmount_1 Amount_2
F100810039136415 218.90 401.90
F100810039137210 218.90 401.90
F100810039180048 218.90 401.90
F100810039181025 218.90 401.90
I have read lot of post but they are with Aggregate function but for me i do not need that functionality here the only thing i need is my Amount column need to splite in two by product_code. Since product has regular valude and markdown value.
Is this possbile?
Please advice,

The real problem with your data (other than it not being in a readily consumable format for us) is that you have absolutely nothing that allows for any method to determine what the previous row is. The data can only be checked for the max amount and the max amount that's not greater than the max amount. In the data you have, it's just as easy for the "latest" amount to be less than the max amount.

Despite what anyone posts, if you trully mean "previous row", then you must have something by date or ID that keeps track of the order of the rows.

--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)

keyun SSCrazy Points: 2397 More actions · Answer 6

Guys,,,this is very good feed back for me to start work on your suggestion. Please accept my big apology that i didnt provide you detail. But in some case where product has more than two price due to Promotion and all other factors.

Let me start work on your suggestion and follow the link that you guys have provider and get back with you in a day.

Note* The unique column i have is customer Account Number which is first column and thats key which associate with Product.

keyun SSCrazy Points: 2397 More actions · Answer 7

Imu92,

I think i need to try your Row_Number() function where you have supply Account, Prod_code as partition by .. I am going to try to use your method and see what i can come up with.

Thanks a million. 🙂

keyun SSCrazy Points: 2397 More actions · Answer 8

Rob, Sorry about not providing more detail but it is true that in some case where Product has more than two price. It is very difficult to say that each product code has fix two different price, Some case product has one price and other product contain up to five different price.

keyun SSCrazy Points: 2397 More actions · Answer 9

Imu92 & Rob,

Here is my sample code where i found out how to do cross tab by using 92's suggestion on RoW_NUMBER() function.

DROP TABLE ##TBL1

CREATE TABLE ##TBL1

(Accountvarchar(20),

Prod_codenumeric(18,0),

AmountMoney

)

INSERT INTO ##TBL1 select 'F1008100391','36415','218.90'

INSERT INTO ##TBL1 select 'F1008100391','36415','401.90'

INSERT INTO ##TBL1 select 'F1008100391','37210','218.90'

INSERT INTO ##TBL1 select 'F1008100391','37210','401.79'

INSERT INTO ##TBL1 select 'F1008100391','80048','218.56'

INSERT INTO ##TBL1 select 'F1008100391','80048','401.90'

INSERT INTO ##TBL1 select 'F1008100391','80048','319.89'

INSERT INTO ##TBL1 select 'F1008100391','80048','699.19'

INSERT INTO ##TBL1 select 'F1008100391','81025','218.90'

INSERT INTO ##TBL1 select 'F1008100391','81025','401.90'

INSERT INTO ##TBL1 select 'F1008100391','81025','399.90'

select * from ##TBL1

Drop Table ##TBL2

select*, ROW_NUMBER() OVER (PARTITION BY Account, Prod_Code ORDER BY Account) as ProcductLineNo

INTO##TBL2

From##TBL1

Order by 1

-- 11

SELECTAccount,

Prod_Code,

MAX(CASE WHEN ProcductLineNo = '1' THEN Amount END ) AS Amount_1,

MAX(CASE WHEN ProcductLineNo = '2' THEN Amount END ) AS Amount_2,

MAX(CASE WHEN ProcductLineNo = '3' THEN Amount END ) AS Amount_3,

MAX(CASE WHEN ProcductLineNo = '4' THEN Amount END ) AS Amount_4

FROM##TBL2

GROUP BY Account, Prod_Code

Order by Account

Now, only the problem is that, as of today i know that there are maximum four different price per product but in case in future if it increase how would i know that i need to add # of Max () statement on my last query. Currently, above script is fine but let me know if you guys have any idea to write a script where it will pull data based on number of "ProductLineNo"??

Again, I really thanks to you guys that you responde me very quickly.:-P

Looking forward to hear from you soon.

Thanks.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

keyun (4/24/2010)
Imu92 & Rob,
Here is my sample code where i found out how to do cross tab by using 92's suggestion on RoW_NUMBER() function.
DROP TABLE ##TBL1
CREATE TABLE ##TBL1
(Accountvarchar(20),
Prod_codenumeric(18,0),
AmountMoney
)
INSERT INTO ##TBL1 select 'F1008100391','36415','218.90'
INSERT INTO ##TBL1 select 'F1008100391','36415','401.90'
INSERT INTO ##TBL1 select 'F1008100391','37210','218.90'
INSERT INTO ##TBL1 select 'F1008100391','37210','401.79'
INSERT INTO ##TBL1 select 'F1008100391','80048','218.56'
INSERT INTO ##TBL1 select 'F1008100391','80048','401.90'
INSERT INTO ##TBL1 select 'F1008100391','80048','319.89'
INSERT INTO ##TBL1 select 'F1008100391','80048','699.19'
INSERT INTO ##TBL1 select 'F1008100391','81025','218.90'
INSERT INTO ##TBL1 select 'F1008100391','81025','401.90'
INSERT INTO ##TBL1 select 'F1008100391','81025','399.90'
select * from ##TBL1
Drop Table ##TBL2
select*, ROW_NUMBER() OVER (PARTITION BY Account, Prod_Code ORDER BY Account) as ProcductLineNo
INTO##TBL2
From##TBL1
Order by 1
-- 11
SELECTAccount,
Prod_Code,
MAX(CASE WHEN ProcductLineNo = '1' THEN Amount END ) AS Amount_1,
MAX(CASE WHEN ProcductLineNo = '2' THEN Amount END ) AS Amount_2,
MAX(CASE WHEN ProcductLineNo = '3' THEN Amount END ) AS Amount_3,
MAX(CASE WHEN ProcductLineNo = '4' THEN Amount END ) AS Amount_4
FROM##TBL2
GROUP BY Account, Prod_Code
Order by Account
Now, only the problem is that, as of today i know that there are maximum four different price per product but in case in future if it increase how would i know that i need to add # of Max () statement on my last query. Currently, above script is fine but let me know if you guys have any idea to write a script where it will pull data based on number of "ProductLineNo"??
Again, I really thanks to you guys that you responde me very quickly.:-P
Looking forward to hear from you soon.
Thanks.

I'll say it again... this is "Slow and Painful Death By SQL" because there is absolutely nothing in the data that indicates its temporal position. The table needs an IDENTITY column or a DATETIME column or, better yet in case of DateTime Ties, BOTH.

--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)