June 9, 2016 at 11:11 am
I have a Sales table with the following schema.
ID, Product, [Quarter One Earnings], [Quarter Two Earnings], [Quarter Three Earnings], [Quarter Four Earnings]
I would like to display the earnings of each product by name horizontally and the result should appear as below
Product Purse Shoes T-Shirt
Quarter One Earnings 15,000,000 16,000,000 13,000,000
Quarter Two Earnings 19,000,000 18,000,000 16,000,000
Quarter Three Earnings 18,800,000 19,000,000 18,500,000
Quarter Four Earnings 21,600,000 20,000,000 19,000,000
I have used the following to try to produce the rusult I need but the result is not what I want
Select * from Sales
pivot (
max(Product) for id in ([1], [2], [3])
) as pvt;
June 9, 2016 at 11:33 am
It's hard to help you write a query with this little to go on.
Any chance you could include the table structure (as a "Create Table" statement, not just text), and an Insert statement with some sample data?
Beyond that, why do a pivot in the query? I prefer to do those in an analytical tool. Excel, Power BI, TableAu, whatever. Dynamic pivots are usually much more useful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2016 at 11:53 am
Hi, Thanks for replying. Here are the create and insert statements
CREATE TABLE [dbo].[Sales](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Product] [nvarchar](25) NOT NULL,
[Quarter One Earnings] int NULL,
[Quarter Two Earnings] int NULL,
[Quarter Three Earnings] int NULL,
[Quarter Four Earnings] int NULL,
PRIMARY KEY (ID)
)
GO
Insert Into Sales (Product, [Quarter One Earnings], [Quarter Two Earnings], [Quarter Three Earnings], [Quarter Four Earnings])
Values ('Purse', 15000000, 19000000,18800000, 21600000);
Insert Into Sales (Product, [Quarter One Earnings], [Quarter Two Earnings], [Quarter Three Earnings], [Quarter Four Earnings])
Values ('Shoes', 16000000, 18000000,19000000, 20000000);
Insert Into Sales (Product, [Quarter One Earnings], [Quarter Two Earnings], [Quarter Three Earnings], [Quarter Four Earnings])
Values ('T-Shirt', 13000000, 16000000,18500000, 19000000);
June 9, 2016 at 11:56 am
You need to unpivot before pivoting again. This can be done in a single statement.
June 9, 2016 at 1:48 pm
I have unpivot the table and although the result is getting close to what I need, it is still not what I want.
Below is the query I've used, please pointout what I've done wrong.
select * from Sales
pivot (max(Product) for ID in ([1],[2],[3])) as p
unpivot (Product for ID in ([1],[2],[3])) as p
June 9, 2016 at 2:04 pm
SQLUSERMAN (6/9/2016)
I have unpivot the table and although the result is getting close to what I need, it is still not what I want.Below is the query I've used, please pointout what I've done wrong.
select * from Sales
pivot (max(Product) for ID in ([1],[2],[3])) as p
unpivot (Product for ID in ([1],[2],[3])) as p
You're taking a shortcut. Pivot and Unpivot depend on the columns you provide. I told you to first unpivot and then pivot, that's not what you did.
Personally, I prefer to use cross tabs instead of pivot and CROSS APPLY with table value constructors instead of unpivot. Both techniques give more flexibility and might give better performance.
June 9, 2016 at 3:20 pm
I'll just leave this here. Be sure to understand the solutions or you'll have lots of trouble maintaining them.
You might want to read the following articles:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
DECLARE @Sales TABLE(
ID int,
Product varchar(100),
[Quarter One Earnings] money,
[Quarter Two Earnings] money,
[Quarter Three Earnings] money,
[Quarter Four Earnings] money);
INSERT INTO @Sales
VALUES
(1, 'Purse ', 15000000, 19000000, 18800000, 21600000),
(2, 'Shoes ', 16000000, 18000000, 19000000, 20000000),
(3, 'T-Shirt', 13000000, 16000000, 18500000, 19000000);
SELECT Period,
MAX( CASE WHEN Product = 'Purse' THEN Value END) AS Purse,
MAX( CASE WHEN Product = 'Shoes' THEN Value END) AS Shoes,
MAX( CASE WHEN Product = 'T-Shirt' THEN Value END) AS [T-Shirt]
FROM @Sales
CROSS APPLY(VALUES ( 1, 'Quarter One Earnings' , [Quarter One Earnings] ),
( 2, 'Quarter Two Earnings' , [Quarter Two Earnings] ),
( 3, 'Quarter Three Earnings', [Quarter Three Earnings]),
( 4, 'Quarter Four Earnings' , [Quarter Four Earnings] ))p(POrder, Period, Value)
GROUP BY POrder, Period
ORDER BY POrder;
SELECT *
FROM (SELECT Period, Product, Value
FROM @Sales
UNPIVOT (Period FOR Value in ([Quarter One Earnings],[Quarter Two Earnings],[Quarter Three Earnings],[Quarter Four Earnings])) as u) u
PIVOT (MAX(Period) FOR Product in ([Purse],[Shoes],[T-Shirt])) as p;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply