July 16, 2010 at 1:35 am
Hi Everyone ,
First of all I have to apologize for my English. It's my second language. I have a problem to do with my sql server 2005.
As I have table structure like this.
Price | Customer | Item
12.00 | customer 6| Carting away
245.00 |customer 6 | Soft rock
98.00 | customer 6| Column footings not exceeding 2m
45.00 | customer 7| Carting away
456.00|customer 7 | Soft rock
56.00 | customer 7| Column footings not exceeding 2m
45.00 | customer 1| Carting away
45.00 | customer 1| Soft rock
56.00 | customer 1| Column footings not exceeding 2m
23.00 | customer 2| Carting away
34.00 | customer 2| Soft rock
456.00|customer 2| Column footings not exceeding 2m
55.00 | customer 3| Carting away
344.00|customer 3 |Soft rock
What I want to do . I want to make query to show top 5 lowest price for each item and use the customer as column like this..
Item | 1 | 2 | 3 | 4 | 5
Soft rock | Customer2 |Customer4 | Customer 6 | Customer 1 | Customer 5
Carting away | Customer 8| Customer 1 | Customer 4 | Customer 3| Customer 7
----
Just like this one.
Every comments will be much appreciated
Thank you
Ty..
July 16, 2010 at 2:18 am
Can you show us what you have got in terms of code already ?
July 16, 2010 at 9:57 am
When you say top 5, is five constant? or do you mean top X, like you said in your title?
If you're ok with having a fixed number of columns, you can do this pretty easily using the pivot operator.
http://www.sqlservercentral.com/Forums/FindPost950883.aspx
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
July 18, 2010 at 9:44 pm
weitzera (7/16/2010)
When you say top 5, is five constant? or do you mean top X, like you said in your title?If you're ok with having a fixed number of columns, you can do this pretty easily using the pivot operator.
Thanks weitzera for your response. I meant Top x . It's not the fixed number of top rows to show. It will dynamic number.
July 18, 2010 at 9:53 pm
Dave Ballantyne (7/16/2010)
Can you show us what you have got in terms of code already ?
This is what I have got so far.
SELECT TOP 7 RESPONSE.RESPONSE_ID , RESPONSE.PRICE , UNIT.NAME , response.heading_id FROM
RESPONSE INNER JOIN HEADING ON HEADING.HEADING_ID = RESPONSE.HEADING_ID
INNER JOIN UNIT ON UNIT.UNIT_ID = RESPONSE.VENDOR_ID
WHERE HEADING.PROJECT_ID = 8 AND HEADING.HEADING_ID = 44 ORDER BY RESPONSE.PRICE
The result will be like this
5|12.00|customer |6|44
26|23.00|customer |2|44
40|39.00|customer |4|44
12|45.00|customer |7|44
19|45.00|customer |1|44
33|55.00|customer |3|44
47|67.00|customer |5|44
This is not what I want because it's obviously that it shows only top 7 values for heading_id = 44
I want to show all top x values for each heading_id but only this is in my mind. As I mention in my detail I want to show
each heading with top x value next to it. like below
heading_id r1 r2 r3 r4 r5 r6 r7
44 | 12.00 | 23.00 | 39.00 | 45.00 | 45.00 | 55.00 | 67.00
55 ....................................................................
71 ..........................................................................
..............................................................................................
Again I have to apologist as my English is totally bad but I really need helps.
I get stuck this thing for 3 days. Yesterday I found some interesting code for select nth row .
I though I can use that for heading to select each nth value.
Still stuck...
Cheers,
July 22, 2010 at 9:51 am
Unfortunately, there's no way to get a variable number of rows to columns. The best you can do from within TSQL is to choose a reasonable maximum number of columns and code for that, simply using nulls in the columns greater than x. i.e. code for 40 values, and let the user choose a value for x <= 40.
Otherwise, you will have to select the top x values, and convert the rows to columns in another tool.
Edit: Well, OK that's not completely true. You could write a dynamic SQL to output the appropriate pivot query for your value of x, up to whatever the maximum number of columns for pivot is. That might not be too difficult.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
July 23, 2010 at 1:38 am
ipeepay (7/16/2010)
Hi Everyone ,First of all I have to apologize for my English. It's my second language. I have a problem to do with my sql server 2005.
As I have table structure like this.
Price | Customer | Item
12.00 | customer 6| Carting away
245.00 |customer 6 | Soft rock
98.00 | customer 6| Column footings not exceeding 2m
45.00 | customer 7| Carting away
456.00|customer 7 | Soft rock
56.00 | customer 7| Column footings not exceeding 2m
45.00 | customer 1| Carting away
45.00 | customer 1| Soft rock
56.00 | customer 1| Column footings not exceeding 2m
23.00 | customer 2| Carting away
34.00 | customer 2| Soft rock
456.00|customer 2| Column footings not exceeding 2m
55.00 | customer 3| Carting away
344.00|customer 3 |Soft rock
What I want to do . I want to make query to show top 5 lowest price for each item and use the customer as column like this..
Item | 1 | 2 | 3 | 4 | 5
Soft rock | Customer2 |Customer4 | Customer 6 | Customer 1 | Customer 5
Carting away | Customer 8| Customer 1 | Customer 4 | Customer 3| Customer 7
----
Just like this one.
Every comments will be much appreciated
Thank you
Ty..
And what would happen with "PRICE" column ?
additonally post your query according to the standards . read the link from my signature.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 23, 2010 at 1:53 am
weitzera (7/22/2010)
Unfortunately, there's no way to get a variable number of rows to columns. The best you can do from within TSQL is to choose a reasonable maximum number of columns and code for that, simply using nulls in the columns greater than x. i.e. code for 40 values, and let the user choose a value for x <= 40.Otherwise, you will have to select the top x values, and convert the rows to columns in another tool.
Edit: Well, OK that's not completely true. You could write a dynamic SQL to output the appropriate pivot query for your value of x, up to whatever the maximum number of columns for pivot is. That might not be too difficult.
Thank you for your response weitzera .
I have already done that by creating function and return table .
By now we fix the number or column which is 5 in the mean time.
The code is look like this
CREATE FUNCTION DBO.GETCHEAPESTPRICE(@PROJECTID INT)
RETURNS @RTB TABLE
(
HEADINGID INT ,
CH1INT,
CH2INT,
CH3INT,
CH4INT,
CH5INT
)
AS
BEGIN
DECLARE @CH1 INT, @CH2 INT ,@CH3 INT , @CH4 INT , @CH5 INT
DECLARE @price INT , @HEADINGID INT
DECLARE CURSORHEADINGS CURSOR FOR
SELECT HEADING_ID FROM HEADING WHERE PROJECT_ID = @PROJECTID
AND VENDOR_PRICE_REQ = 1
OPEN CURSORHEADINGS
FETCH NEXT FROM CURSORHEADINGS INTO @HEADINGID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CURSORPRICE CURSOR FOR
SELECT TOP 5 RESPONSE.RESPONSE_ID FROM
RESPONSE INNER JOIN HEADING ON HEADING.HEADING_ID = RESPONSE.HEADING_ID
WHERE HEADING.PROJECT_ID = @PROJECTID AND HEADING.HEADING_ID = @HEADINGID
AND (ISNULL(dbo.RESPONSE.UNITS, 0) <> 0) AND (ISNULL(dbo.RESPONSE.PRICE, 0) <> 0)
ORDER BY RESPONSE.PRICE
OPEN CURSORPRICE
FETCH NEXT FROM CURSORPRICE INTO @price
DECLARE @COUNTER INT
SET @COUNTER = 1
WHILE @@FETCH_STATUS = 0
BEGIN
IF @COUNTER = 1
BEGIN
INSERT INTO @RTB (HEADINGID, CH1) VALUES (@HEADINGID, @price)
END
ELSE IF @COUNTER = 2
BEGIN
UPDATE @RTb SET CH2 = @price WHERE HEADINGID = @HEADINGID
END
ELSE IF @COUNTER = 3
BEGIN
UPDATE @RTb SET CH3 = @price WHERE HEADINGID = @HEADINGID
END
ELSE IF @COUNTER = 4
BEGIN
UPDATE @RTb SET CH4 = @price WHERE HEADINGID = @HEADINGID
END
ELSE IF @COUNTER = 5
BEGIN
UPDATE @RTB SET CH5 = @price WHERE HEADINGID = @HEADINGID
END
SET @COUNTER = @COUNTER +1
FETCH NEXT FROM CURSORPRICE INTO @price
END
CLOSE CURSORPRICE
DEALLOCATE CURSORPRICE
FETCH NEXT FROM CURSORHEADINGS INTO @HEADINGID
END
CLOSE CURSORHEADINGS
DEALLOCATE CURSORHEADINGS
RETURN
END
As you mentions about Pivot . Yes I thought about that at first but as we wouldn't know the number of prices that we want to know so that's why I didn't use the pivot.
I think I will create function that handle dynamic number of prices next time.
Many thanks.
July 23, 2010 at 1:54 am
Thanks Bhuvnesh , I will have a look at it.
😀
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply