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.
Author |
Topic |
MegaScript
Starting Member
3 Posts |
Posted - 2013-01-15 : 14:47:29
|
Hi, I have an interesting problem that I'm trying to solve. I have a transaction table which I'm trying to generate a report out of. For the sake of clarity, here's a sample of what I have in my table:MemberID - Product - TotalQuantity------------------------------------mem1 - shoe1 - 150mem1 - shoe2 - 160mem1 - shoe3 - 200mem1 - shoe4 - 1600mem1 - shoe5 - 16mem1 - shoe6 - 60mem1 - shoe7 - 150mem1 - shoe8 - 90mem1 - shoe9 - 10------------------------------------Now the table above has multiple members but I'll just use mem1 as an example. What I'm basically doing here is getting the distinct records based on the Product and the total quantity bought over a period of time. What I need however is to provide a report which provides the top 5 products (Based on the quantity) per customer, and the way they want to show it is this way:MemberID - Product1-TotalQuantity - Product2-TotalQuantity - Product2-TotalQuantity - Product4-TotalQuantity - Product5-TotalQuantity----------------------------------------------------------------------mem1 - shoe4-1600 - shoe3-200 - shoe2-160 - shoe7-150 - shoe1-140mem2 (Top 5 products like the above)mem3 (Same as the above)mem4 (Same as the above)----------------------------------------------------------------------I tried to work with PIVOT but I'm a bit confused there and I'm having a hard time trying to accomplish this, any ideas?Thanks. |
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 15:14:40
|
Hi,Is this what you wanted?SET NO COUNT ON;GOIF OBJECT_ID('CountTest', 'U') IS NOT NULL DROP TABLE CountTest;GOCREATE TABLE CountTest ( MemberID nvarchar(4) ,ProductID nvarchar(5) ,TotalQuantity int )GOINSERT INTO CountTestValues ('Mem1', 'Shoe1', 76);INSERT INTO CountTestValues ('Mem1', 'Shoe2', 28);INSERT INTO CountTestValues ('Mem1', 'Shoe3', 42);INSERT INTO CountTestValues ('Mem2', 'Shoe1', 91);INSERT INTO CountTestValues ('Mem2', 'Shoe2', 28);INSERT INTO CountTestValues ('Mem2', 'Shoe3', 64);INSERT INTO CountTestValues ('Mem3', 'Shoe1', 72);INSERT INTO CountTestValues ('Mem3', 'Shoe2', 31);INSERT INTO CountTestValues ('Mem3', 'Shoe3', 69);SELECT *FROM ( SELECT MemberID ,ProductID ,TotalQuantity FROM CountTest ) X PIVOT ( SUM(TotalQuantity) FOR ProductID IN ([Shoe1], [Shoe2], [Shoe3]) ) YMemberID Shoe1 Shoe2 Shoe3-------- ----------- ----------- -----------Mem1 76 28 42Mem2 91 28 64Mem3 72 31 69(3 row(s) affected)IF OBJECT_ID('CountTest', 'U') IS NOT NULL DROP TABLE CountTest; |
|
|
MegaScript
Starting Member
3 Posts |
Posted - 2013-01-15 : 15:25:10
|
Hi Robowski,The code you provided produces the following:MemberID - Shoe1 - Shoe2 - Shoe3---------------------------------mem1 - NULL - NULL - NULL---------------------------------I guess you're using the product code as column headers? The product should be a field, not a header, because I'm trying to retrieve the top 5 products based on quantity for each user, so we could have something like the following:MemberID - 1 - 2 - 3 - 4 - 5-----------------------------mem1 - p1 -p2 -p5 -p9 -p10mem2 - p3 -p4 -p1 -p2 -p6mem3 - p2 -p4 -p3 -p9 -p8-----------------------------The position of the product is determined by the total quantity it sold for that customer. The highest would be under column 1. |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 16:03:12
|
quote: Originally posted by MegaScript Hi Robowski,The code you provided produces the following:MemberID - Shoe1 - Shoe2 - Shoe3---------------------------------mem1 - NULL - NULL - NULL---------------------------------I guess you're using the product code as column headers? The product should be a field, not a header, because I'm trying to retrieve the top 5 products based on quantity for each user, so we could have something like the following:MemberID - 1 - 2 - 3 - 4 - 5-----------------------------mem1 - p1 -p2 -p5 -p9 -p10mem2 - p3 -p4 -p1 -p2 -p6mem3 - p2 -p4 -p3 -p9 -p8-----------------------------The position of the product is determined by the total quantity it sold for that customer. The highest would be under column 1.
It runs fine for me, I noticed there I put a space in the no count?Does it have to come back as a PIVOT format? I guess it could be done with formatting, but would this suffice? (this is a test for returning 2 records when there are three for each)SET NOCOUNT ON;GOIF OBJECT_ID('CountTest', 'U') IS NOT NULL DROP TABLE CountTest;GOCREATE TABLE CountTest ( MemberID int ,ProductID nvarchar(5) ,TotalQuantity int )GOINSERT INTO CountTestValues ('1342', 'Shoe1', 76);INSERT INTO CountTestValues ('1342', 'Shoe2', 28);INSERT INTO CountTestValues ('1342', 'Shoe3', 42);INSERT INTO CountTestValues ('3527', 'Shoe1', 91);INSERT INTO CountTestValues ('3527', 'Shoe2', 28);INSERT INTO CountTestValues ('3527', 'Shoe3', 64);INSERT INTO CountTestValues ('6184', 'Shoe1', 15);INSERT INTO CountTestValues ('6184', 'Shoe2', 31);INSERT INTO CountTestValues ('6184', 'Shoe3', 69);WITH CTERanked(MemberID, ProductID, QuantityTotal, Top5)AS ( SELECT MemberID ,ProductID ,TotalQuantity ,RANK() OVER (PARTITION BY MemberID ORDER BY TotalQuantity DESC) AS 'Top5' FROM CountTest )SELECT *FROM CTERankedWHERE Top5 < 3 |
|
|
MegaScript
Starting Member
3 Posts |
Posted - 2013-01-15 : 17:27:23
|
Hi Robowski, it does not have to be a PIVOT. So long as it returns top 5 per client. I think worse comes to worse I'm just going to write a quick VB or JScript to get this done in multiple steps. I just thought I could save time by running queries instead.Thanks for your help. |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 18:03:10
|
quote: Originally posted by MegaScript Hi Robowski, it does not have to be a PIVOT. So long as it returns top 5 per client. I think worse comes to worse I'm just going to write a quick VB or JScript to get this done in multiple steps. I just thought I could save time by running queries instead.Thanks for your help.
I have it for you, just tidying it up - 10 MINS! |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-01-15 : 18:11:33
|
This runs for me and returns the data I would expect it to..Probably is a better way to script it, but give it a try for now![Code]SET NOCOUNT ON;GOIF OBJECT_ID('CountTest', 'U') IS NOT NULLDROP TABLE CountTest;GOIF OBJECT_ID('TestTempy', 'U') IS NOT NULLDROP TABLE TestTempy;GOIF OBJECT_ID('Temp1', 'U') IS NOT NULLDROP TABLE Temp1;GOCREATE TABLE TestTempy ( MemberID int ,Top1 nvarchar(20) ,Top2 nvarchar(20) ,Top3 nvarchar(20) ,Top4 nvarchar(20) ,Top5 nvarchar(20) --,Prank int --,ProwNum int );GOCREATE TABLE CountTest(MemberID int,ProductID nvarchar(5),TotalQuantity int)GOINSERT INTO CountTestValues ('1342', 'Shoe1', 76);INSERT INTO CountTestValues ('1342', 'Shoe2', 28);INSERT INTO CountTestValues ('1342', 'Shoe3', 42);INSERT INTO CountTestValues ('1342', 'Shoe4', 16);INSERT INTO CountTestValues ('1342', 'Shoe5', 23);INSERT INTO CountTestValues ('1342', 'Shoe6', 45);INSERT INTO CountTestValues ('3527', 'Shoe1', 91);INSERT INTO CountTestValues ('3527', 'Shoe2', 28);INSERT INTO CountTestValues ('3527', 'Shoe3', 64);INSERT INTO CountTestValues ('3527', 'Shoe4', 99);INSERT INTO CountTestValues ('3527', 'Shoe5', 24);INSERT INTO CountTestValues ('3527', 'Shoe6', 67);INSERT INTO CountTestValues ('6184', 'Shoe1', 15);INSERT INTO CountTestValues ('6184', 'Shoe2', 31);INSERT INTO CountTestValues ('6184', 'Shoe3', 69);INSERT INTO CountTestValues ('6184', 'Shoe4', 81);INSERT INTO CountTestValues ('6184', 'Shoe5', 5);INSERT INTO CountTestValues ('6184', 'Shoe6', 36);WITH CTERanked(MemberID, ProductID, totalQuantity, Top5)AS ( SELECT MemberID ,ProductID ,TotalQuantity ,RANK() OVER (PARTITION BY MemberID ORDER BY TotalQuantity DESC) AS 'Top5' --,ROW_NUMBER() OVER (ORDER BY Top5 DESC) AS 'RowNum' FROM CountTest ) ,CTERowNumber(MemberID, ProductID, QuantityTotal, Top5, RowNum) AS ( SELECT MemberID ,ProductID ,TotalQuantity ,Top5 ,ROW_NUMBER() OVER (ORDER BY MemberID) AS 'RowNum' FROM CTERanked WHERE Top5 < 6 )SELECT * INTO Temp1 FROM CTERowNumberDECLARE @Max int, @Mem intSET @Max = 50 WHILE @Max > 0 BEGIN INSERT INTO TestTempy (MemberID) SELECT MemberID FROM AnotherTemp WHERE RowNum = @Max UPDATE TestTempy SET Top1 = AnotherTemp.ProductID + ' ' + CAST(AnotherTemp.QuantityTotal AS nvarchar(30)) FROM AnotherTemp JOIN TestTempy ON TestTempy.MemberID = AnotherTemp.MemberID WHERE TestTempy.MemberID = @Mem AND RoWNum = @Max SET @Max = (@MAX - 1) SET @Mem = (SELECT MemberID FROM AnotherTemp WHERE RowNum = @Max) UPDATE TestTempy SET Top2 = AnotherTemp.ProductID + ' ' + CAST(AnotherTemp.QuantityTotal AS nvarchar(30)) FROM AnotherTemp JOIN TestTempy ON TestTempy.MemberID = AnotherTemp.MemberID WHERE TestTempy.MemberID = @Mem AND RoWNum = @Max SET @Max = (@MAX - 1) SET @Mem = (SELECT MemberID FROM AnotherTemp WHERE RowNum = @Max) UPDATE TestTempy SET Top3 = AnotherTemp.ProductID + ' ' + CAST(AnotherTemp.QuantityTotal AS nvarchar(30)) FROM AnotherTemp JOIN TestTempy ON TestTempy.MemberID = AnotherTemp.MemberID WHERE TestTempy.MemberID = @Mem AND RoWNum = @Max SET @Max = (@MAX - 1) SET @Mem = (SELECT MemberID FROM AnotherTemp WHERE RowNum = @Max) UPDATE TestTempy SET Top4 = AnotherTemp.ProductID + ' ' + CAST(AnotherTemp.QuantityTotal AS nvarchar(30)) FROM AnotherTemp JOIN TestTempy ON TestTempy.MemberID = AnotherTemp.MemberID WHERE TestTempy.MemberID = @Mem AND RoWNum = @Max SET @Max = (@MAX - 1) SET @Mem = (SELECT MemberID FROM AnotherTemp WHERE RowNum = @Max) UPDATE TestTempy SET Top5 = AnotherTemp.ProductID + ' ' + CAST(AnotherTemp.QuantityTotal AS nvarchar(30)) FROM AnotherTemp JOIN TestTempy ON TestTempy.MemberID = AnotherTemp.MemberID WHERE TestTempy.MemberID = @Mem AND RoWNum = @Max SET @Max = (@MAX - 1) SET @Mem = (SELECT MemberID FROM AnotherTemp WHERE RowNum = @Max) ENDSELECT * FROM TestTempyIF OBJECT_ID('CountTest', 'U') IS NOT NULLDROP TABLE CountTest;GOIF OBJECT_ID('TestTempy', 'U') IS NOT NULLDROP TABLE TestTempy;GOIF OBJECT_ID('Temp1', 'U') IS NOT NULLDROP TABLE Temp1;GO[/code] |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-16 : 00:43:58
|
[code];WITH CTE AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY MemberId ORDER BY TotalQuantity DESC) rn FROM CountTest)INSERT INTO TestTempySELECT MemberID,MAX( CASE WHEN rn = 5 THEN ProductID + ' ' +CAST(TotalQuantity AS varchar) END) Top1,MAX( CASE WHEN rn = 4 THEN ProductID + ' ' +CAST(TotalQuantity AS varchar) END) Top2,MAX( CASE WHEN rn = 3 THEN ProductID + ' ' +CAST(TotalQuantity AS varchar) END) Top3,MAX( CASE WHEN rn = 2 THEN ProductID + ' ' +CAST(TotalQuantity AS varchar) END) Top4,MAX( CASE WHEN rn = 1 THEN ProductID + ' ' +CAST(TotalQuantity AS varchar) END) Top5FROM CTEGROUP BY MemberIDSELECT * FROM TestTempy[/code]--Chandu |
|
|
|
|
|
|
|