Pivot Help
-
Hi Guys
I need help pivoting the following
SELECT [ID],[code_name],,[retail_price] FROM products
1 | Rice & Beans | Small | 10.00
2 | Rice & Beans | Medium | 15.00
3 | Rice & Beans | Large | 20.00
TO be Pivot as follow
SELECT [DESC],[Small],[Medium],[Large] FROM Pivot_Table
Rice & Beans | 10.00 | 15.00 | 20.00
This is my code but it is not working as expected, I am getting NULL value for the description
SELECT [Desc], ISNULL([small],0) AS Small,ISNULL([medium],0) AS Medium,ISNULL([large],0) AS Large
FROM
(
SELECT [code_name],,[retail_price] FROM products
) p
PIVOT
(
SUM([retail_price])
FOR [Size] IN ([Desc],[small],[medium],[large])
) AS pvt- This topic was modified 1 month ago by mashispano.
-
Howdy and welcome aboard!
Please see the article at the first link in my signature line below for how to post "Readily Consumable Data" to help us give you the best help possible as soon as possible. The article contains one method using "UNION ALL" and the following example uses "VALUES".
--===== Create the readily consumable data,
-- which explains everything we need to
-- know about the data, as well.
DROP TABLE IF EXISTS #Products;
GO
CREATE TABLE #Products
(
ID int
,[DESC] varchar(20)
,code_name varchar(20)
,retail_price decimal(9,2)
)
;
INSERT INTO #Products WITH (TABLOCK) --Habit for "Minimally Logged" performance
VALUES (1,'Rice & Beans','Small' ,10.00)
,(2,'Rice & Beans','Medium',15.00)
,(3,'Rice & Beans','Large' ,20.00)
;
GOThen we can say "Sure... no problem. Here's the code and the results". You should always test your code to make sure that it works 🙂
--===== This is known as a CROSSTAB. It's faster and more
-- flexible than PIVOT. See the following article, as well.
-- https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1
SELECT [DESC]
,Small = SUM(IIF(code_name = 'Small' ,retail_price,NULL))
,Medium = SUM(IIF(code_name = 'Medium',retail_price,NULL))
,Large = SUM(IIF(code_name = 'Large' ,retail_price,NULL))
FROM #Products
GROUP BY [DESC]
ORDER BY [DESC]
;Results:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Jeff for your reply, it worked like a charm.
Is it possible to add the column ID as part of the whole result, when I try getting the column ID once added to the query and Group
the result is broken down by ID instead of group by the DESC (code_name)
See your sample with column [ID]
DROP TABLE IF EXISTS #Products;
GO
CREATE TABLE #Products
(
ID int
,[DESC] varchar(20)
,code_name varchar(20)
,retail_price decimal(9,2)
)
;
INSERT INTO #Products WITH (TABLOCK) --Habit for "Minimally Logged" performance
VALUES (1,'Rice & Beans','Small' ,10.00)
,(2,'Rice & Beans','Medium',15.00)
,(3,'Rice & Beans','Large' ,20.00)
;
GO
SELECT [DESC]
,Small = SUM(IIF(code_name = 'Small' ,retail_price,NULL))
,Medium = SUM(IIF(code_name = 'Medium',retail_price,NULL))
,Large = SUM(IIF(code_name = 'Large' ,retail_price,NULL))
,[ID]
FROM #Products
GROUP BY [DESC],[ID]
ORDER BY [DESC]
;
Thanks
Al
-
You could do as below. You can't really GROUP BY ID, because that would separate the Small, Medium and Large into separate rows.
SELECT
[Desc],
Small_ID = MAX(CASE WHEN code_name = 'Small' THEN ID END),
Small = MAX(CASE WHEN code_name = 'Small' THEN retail_price END),
Medium_ID = MAX(CASE WHEN code_name = 'Medium' THEN ID END),
Medium = MAX(CASE WHEN code_name = 'Medium' THEN retail_price END),
Large_ID = MAX(CASE WHEN code_name = 'Large' THEN ID END),
Large = MAX(CASE WHEN code_name = 'Large' THEN retail_price END)
FROM #Products
GROUP BY [Desc]
ORDER BY [Desc]SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
If you don't want to do a group by pivot, but simply want to place the price in a separate column, something simple like this might work for you:
--===== Create the readily consumable data,
-- which explains everything we need to
-- know about the data, as well.
DROP TABLE IF EXISTS #Products;
GO
CREATE TABLE #Products
(
ID int
,code_name varchar(20)
,size varchar(20)
,retail_price decimal(9,2)
)
;
INSERT INTO #Products WITH (TABLOCK) --Habit for "Minimally Logged" performance
VALUES (1,'Rice & Beans','Small' ,10.00)
,(2,'Rice & Beans','Medium',15.00)
,(3,'Rice & Beans','Large' ,20.00)
;
GO
-- Simply put the price in separate columns based on the size column value:
select
Id,
code_name,
case when size = 'Small' then retail_price end as PriceSmall,
case when size = 'Medium' then retail_price end as PriceMedium,
case when size = 'Large' then retail_price end as PriceLarge
from #ProductsOutput:
Id code_name PriceSmall PriceMedium PriceLarge
----------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 Rice & Beans 10.00 NULL NULL
2 Rice & Beans NULL 15.00 NULL
3 Rice & Beans NULL NULL 20.00Since you have a SUM in your example code, this might be too simple for you though.
-
Arrgh! At least in my browser the output from my post above isn't rendered correctly, even though I have used the Plain type format and it shows correctly when I try to edit the post. Yet another case of the site not supporting its users, I guess.
-
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply