SET NO COUNT ON;
GO

IF OBJECT_ID('CountTest', 'U') IS NOT NULL
	DROP TABLE CountTest;

GO

CREATE TABLE CountTest
	(
	MemberID nvarchar(4)
	,ProductID nvarchar(5)
	,TotalQuantity int
	)
GO

INSERT INTO CountTest
Values ('Mem1', 'Shoe1', 76);
INSERT INTO CountTest
Values ('Mem1', 'Shoe2', 28);
INSERT INTO CountTest
Values ('Mem1', 'Shoe3', 42);

INSERT INTO CountTest
Values ('Mem2', 'Shoe1', 91);
INSERT INTO CountTest
Values ('Mem2', 'Shoe2', 28);
INSERT INTO CountTest
Values ('Mem2', 'Shoe3', 64);

INSERT INTO CountTest
Values ('Mem3', 'Shoe1', 72);
INSERT INTO CountTest
Values ('Mem3', 'Shoe2', 31);
INSERT INTO CountTest
Values ('Mem3', 'Shoe3', 69);




SELECT
	*
FROM
	(
	SELECT
		MemberID
		,ProductID
		,TotalQuantity
	FROM
		CountTest
	) X
		PIVOT
	(
	 SUM(TotalQuantity)
	 FOR ProductID
		IN ([Shoe1], [Shoe2], [Shoe3])
	) Y

MemberID Shoe1       Shoe2       Shoe3
-------- ----------- ----------- -----------
Mem1     76          28          42
Mem2     91          28          64
Mem3     72          31          69

(3 row(s) affected)

IF OBJECT_ID('CountTest', 'U') IS NOT NULL
	DROP TABLE CountTest;

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 -p10
mem2 - p3 -p4 -p1 -p2 -p6
mem3 - 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.

---

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.

---

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;
GO

IF OBJECT_ID('CountTest', 'U') IS NOT NULL
DROP TABLE CountTest;
GO

IF OBJECT_ID('TestTempy', 'U') IS NOT NULL
DROP TABLE TestTempy;
GO

IF OBJECT_ID('Temp1', 'U') IS NOT NULL
DROP TABLE Temp1;
GO


CREATE TABLE TestTempy
	(
	MemberID int
	,Top1 nvarchar(20)
	,Top2 nvarchar(20)
	,Top3 nvarchar(20)
	,Top4 nvarchar(20)
	,Top5 nvarchar(20)
	--,Prank int
	--,ProwNum int
	);
GO

CREATE TABLE CountTest
(
MemberID int
,ProductID nvarchar(5)
,TotalQuantity int
)
GO

INSERT INTO CountTest
Values ('1342', 'Shoe1', 76);
INSERT INTO CountTest
Values ('1342', 'Shoe2', 28);
INSERT INTO CountTest
Values ('1342', 'Shoe3', 42);
INSERT INTO CountTest
Values ('1342', 'Shoe4', 16);
INSERT INTO CountTest
Values ('1342', 'Shoe5', 23);
INSERT INTO CountTest
Values ('1342', 'Shoe6', 45);


INSERT INTO CountTest
Values ('3527', 'Shoe1', 91);
INSERT INTO CountTest
Values ('3527', 'Shoe2', 28);
INSERT INTO CountTest
Values ('3527', 'Shoe3', 64);
INSERT INTO CountTest
Values ('3527', 'Shoe4', 99);
INSERT INTO CountTest
Values ('3527', 'Shoe5', 24);
INSERT INTO CountTest
Values ('3527', 'Shoe6', 67);

INSERT INTO CountTest
Values ('6184', 'Shoe1', 15);
INSERT INTO CountTest
Values ('6184', 'Shoe2', 31);
INSERT INTO CountTest
Values ('6184', 'Shoe3', 69);
INSERT INTO CountTest
Values ('6184', 'Shoe4', 81);
INSERT INTO CountTest
Values ('6184', 'Shoe5', 5);
INSERT INTO CountTest
Values ('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 CTERowNumber


DECLARE @Max int, @Mem int

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

			
			
		END

SELECT * FROM TestTempy

IF OBJECT_ID('CountTest', 'U') IS NOT NULL
DROP TABLE CountTest;
GO

IF OBJECT_ID('TestTempy', 'U') IS NOT NULL
DROP TABLE TestTempy;
GO

IF OBJECT_ID('Temp1', 'U') IS NOT NULL
DROP TABLE Temp1;
GO

;WITH CTE AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY MemberId ORDER BY TotalQuantity DESC) rn FROM CountTest)
INSERT INTO TestTempy
SELECT 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) Top5
FROM CTE
GROUP BY MemberID

SELECT * FROM TestTempy