Do i need to use pivot? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Do i need to use pivot?

Author

Topic

freekrubens
Starting Member

4 Posts

Posted - 2010-09-30 : 07:53:06

Hi Guys,

I'm new to the forum so i'd like to thank you first to take a look at my problem.
The situation is quiet simple. I have 2 tables:
- a tabel with product information
- No_
- Description
- Category
- a table with purchase prices
- No_
- purchase_price

The data look like this:
product information:
No_ Description Category
12244 beer glass glass
25200 patato knife knifes
21458 grill pan cookware

purchase prices:
No_ purchase_price
12244 3,00
25200 2,75
25200 2,81
12244 3,11
21458 26,38
12244 3,08

As you notice the table purchase prices containst an entry for every time this item is purchase. The number of times an item is purchased is not fixed. It could be 1 or 2 or 8 times. I can say it won't be more then 10 times.
In this case item 12244 was purchased 3 times, 255200 was purchased 2 times and 21458 was purchased 1 time.

Now, i'm looking for a query that can display 1 table containing all this data in the following format:

No_ Description Category pp1 pp2 pp3
12244 beer glass glass 3,00 3,11 3,08
25200 patato knife knifes 2,75 2,81
21458 grill pan cookware 26,38

I know that there PIVOT can transform row data to column data but from my understanding i need to use an agregated function. But in my second table i have nothing to group or sum, do i?

What query should i use to get the desired result?

Regards,

Freek

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-30 : 08:39:36

Try this:
#assuming that the purchase is not going to be more than 10.


SELECT no,description,category ,[1],[2],[3],[4],[5],[6],[7],,[9],[10]
FROM
(
SELECT P.no,description,category,purchase_price,rank() OVER(Partition by P.no ORDER BY P.purchase_price) AS PurNum

FROM product_information I
INNER JOIN purchase_prices P
		ON I.no=P.no
	)A	
PIVOT (MAX(purchase_price) FOR PurNum IN([1],[2],[3],[4],[5],[6],[7],,[9],[10]))	AS pvot

freekrubens
Starting Member

4 Posts

Posted - 2010-09-30 : 09:31:44

Hi rohitvishwakarma,

I'm not sure what i'me doing but this code works!

I really would like to understand what this statement does.
This part: rank() OVER(Partition... a have never used before.
If you find the time to explain in 2 words what it does exactly do here, that would be great.
If not, a bigg thanks for the solution!

regards,
Freek

freekrubens
Starting Member

4 Posts

Posted - 2010-09-30 : 09:50:28

I did notice something in the result.
Lets say record A has 2 purchase prices and record B has 2 purchase prices.
In the result I have 10 columns for the prices but the result is displayed for like this:

NO_ 1 2 3 4 5 6 7 8 9
A 3,00 3,11 NULL NULL NULL NULL NULL NULL NULL
B 2,50 NULL 2,49 NULL NULL NULL NULL NULL NULL

Why are the prices for record B displayed in column 1 and 3 in stead off column 1 and 2 like record A?

Many thanks for your support!

regards,
Freek

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-30 : 10:00:29

quote:
Originally posted by freekrubens

I did notice something in the result.
Lets say record A has 2 purchase prices and record B has 2 purchase prices.
In the result I have 10 columns for the prices but the result is displayed for like this:

NO_ 1 2 3 4 5 6 7 8 9
A 3,00 3,11 NULL NULL NULL NULL NULL NULL NULL
B 2,50 NULL 2,49 NULL NULL NULL NULL NULL NULL

Why are the prices for record B displayed in column 1 and 3 in stead off column 1 and 2 like record A?

Many thanks for your support!

regards,
Freek

I have got the following sample data in my tables:

product_information

no description category
12244 beer glass glass
25200 potato knife knife
21458 grill pan cookware

purchase_prices

no purchase_price
12244 300
25200 275
25200 281
12244 311
21458 2638
12244 308
21458 5000
21458 1231

and my result using the query:



SELECT no,description,category ,[1],[2],[3],[4],[5],[6],[7],,[9],[10]
FROM
(
SELECT P.no,description,category,purchase_price,rank() OVER(Partition by P.no ORDER BY P.purchase_price) AS PurNum

FROM product_information I
INNER JOIN purchase_prices P
		ON I.no=P.no
	)A
PIVOT (SUM(purchase_price) FOR PurNum IN([1],[2],[3],[4],[5],[6],[7],,[9],[10]))	AS pvot

is:

[no][description ][category][1][2][3][4][5][6][7]

[9][10]
12244 beer glass glass 300 308 311 NULL NULL NULL NULL NULL NULL NULL
21458 grill pan cookware 1231 2638 5000 NULL NULL NULL NULL NULL NULL NULL
25200 potato knife knife 275 281 NULL NULL NULL NULL NULL NULL NULL NULL

and there is no null for me in between

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-30 : 10:05:52

run this query:


SELECT P.no,description,category,purchase_price,rank() OVER(Partition by P.no ORDER BY P.purchase_price) AS PurNum

FROM product_information I
INNER JOIN purchase_prices P
		ON I.no=P.no

and the o/p using my sample data is:

no description category purchase_price PurNum
12244 beer glass glass 300 1
12244 beer glass glass 308 2
12244 beer glass glass 311 3
21458 grill pan cookware 1231 1
21458 grill pan cookware 2638 2
21458 grill pan cookware 5000 3
25200 potato knife knife 275 1
25200 potato knife knife 281 2

The Rank() will rank the column within a group defined by Partition by(in our case it is no i.e product number).
There is also ORDER BY present in the query which will order the data within a group on purchase_price in Ascending order

for eg: take the case of product number 12244 the purchase price is ranked in ascending order and the ranks given are 1,2 and 3.
Similarly the ranks for other two groups i.e 21458 & 25200 are
1,2,3 & 1,2 respectively.

After doing the pivot operation on the Rank we will get the columns 1,2 & 3 filled for products 12244 & 21458 and columns 1 & 2 filled for 25200.

Hope I explained it well. working in office

If not feel free to post here

Thanks
Rohit

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-30 : 10:31:48

for further reading go throughthe following link:

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/

and

http://msmvps.com/blogs/martinpoon/archive/2006/09/22/SQL-Server-2005-ranking-functions-_2D00_-RANK_280029002C00_-DENSE_5F00_RANK_280029002C00_-NTILE_28002900_-.aspx

freekrubens
Starting Member

4 Posts

Posted - 2010-09-30 : 11:20:27

Thanks again for the answer.
I'm leaving for 1 week holiday now but will come back to this isuue.
A ran the query that you asked and it returned value 3 but there where only 2 lines in de table.

I did use other data to run your code, but most of the data looks ok except for the NULL values in between.

regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources