Pivot or other solution needed! Please!!

Question

Hi- I'm new to SQL and am stuck on this one. I've been trying to use a pivot function but am not having any luck. I'm extremely grateful to anyone who can help me out!

Have:

Need:

Answer 1

Hi @John Marchbanks ,

Pease try the following solution.

Unfortunately, this site is blocking T-SQL as a text.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, _Date DATE, _Type VARCHAR(50), _Value INT);  
INSERT @tbl (_Date, _Type, _Value) VALUES  
('2022-03-03', 'Apples', 43),  
('2021-07-19', 'Oranges', 25),  
('2022-03-03', 'Oranges', 53),  
('2021-07-19', 'Apples', 33);  
-- DDL and sample data population, end  
  
SELECT  _Date  
   , MAX(IIF(_Type = 'Apples', _Value, NULL)) AS Apples_Value  
   , MAX(IIF(_Type = 'Oranges', _Value, NULL)) AS Oranges_Value  
FROM @tbl  
GROUP BY _Date  
ORDER BY _Date;  

Output

+------------+--------------+---------------+  
|   _Date    | Apples_Value | Oranges_Value |  
+------------+--------------+---------------+  
| 2021-07-19 |           33 |            25 |  
| 2022-03-03 |           43 |            53 |  
+------------+--------------+---------------+  

Answer 2

Thanks for the help!

Answer 3

Hi @John Marchbanks
Try this query using PIVOT:

DECLARE @tbl TABLE (_Date DATE, _Type VARCHAR(50), _Value INT);  
INSERT @tbl (_Date, _Type, _Value) VALUES  
 ('2022-03-03', 'Apples', 43),  
 ('2021-07-19', 'Oranges', 25),  
 ('2022-03-03', 'Oranges', 53),  
 ('2021-07-19', 'Apples', 23);  
  
SELECT *  
FROM @tbl  
PIVOT(MAX(_Value)FOR _Type IN([Apples],[Oranges]))P  
ORDER BY _Date;  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.