SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple columns from one column.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MegaScript
Starting Member

3 Posts

Posted - 01/15/2013 :  14:47:29  Show Profile  Reply with Quote
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 - 150
mem1 - shoe2 - 160
mem1 - shoe3 - 200
mem1 - shoe4 - 1600
mem1 - shoe5 - 16
mem1 - shoe6 - 60
mem1 - shoe7 - 150
mem1 - shoe8 - 90
mem1 - 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-140
mem2 (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 - 01/15/2013 :  15:14:40  Show Profile  Reply with Quote
Hi,

Is this what you wanted?



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;


Edited by - Robowski on 01/15/2013 15:15:56
Go to Top of Page

MegaScript
Starting Member

3 Posts

Posted - 01/15/2013 :  15:25:10  Show Profile  Reply with Quote
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.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  16:03:12  Show Profile  Reply with Quote
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.



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

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

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 ('3527', 'Shoe1', 91);
INSERT INTO CountTest
Values ('3527', 'Shoe2', 28);
INSERT INTO CountTest
Values ('3527', 'Shoe3', 64);

INSERT INTO CountTest
Values ('6184', 'Shoe1', 15);
INSERT INTO CountTest
Values ('6184', 'Shoe2', 31);
INSERT INTO CountTest
Values ('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
CTERanked
WHERE
Top5 < 3
Go to Top of Page

MegaScript
Starting Member

3 Posts

Posted - 01/15/2013 :  17:27:23  Show Profile  Reply with Quote
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.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  18:03:10  Show Profile  Reply with Quote
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!
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  18:11:33  Show Profile  Reply with Quote
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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/16/2013 :  00:43:58  Show Profile  Reply with Quote

;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


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000