Multiple columns from one column.

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

Multiple columns from one column.

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 - 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 - 2013-01-15 : 15:14:40

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;

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 -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.

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

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

[/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 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[/code]

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources