August 26, 2017 at 10:19 am
All I need is convert Column(A1/A2) into rows and rows(1 into Jan) into columns.Input:
Here A1/A2 belongs to say A and they are calculated as A1/A2 for each month.
| Month | A1 | A2 | B1 | B2 | C1 | C2 |
| 1 | 120 | 60 | 40 | 80 | 120 | 120 |
| 2 | 50 | 50 | 40 | 20 | 60 | 30 |
| 3 | 50 | 25 | 40 | 10 | 90 | 30 |
I need below o/p without using pivot and unpivot
O/P:
| X | Jan(1 is denoting Jan) | Feb | Mar |
| A | 120/60(calculation:A1/A2) | 40/80 | 120/120 |
| B | 50/50 | 40/20 | 60/30 |
| C | 50/25 | 40/10 | 90/30 |
I tried but my query is too long as I am using case and Union All three times each For A1 A2,B1 B2,C1 C2 etc.
Can you pls help me out.
Thanks in advance
August 26, 2017 at 5:24 pm
First, you should learn to post readily consumable data instead of just plain text. Please see the first link under "Helpful Links" in my signature line below. People will answer much more quickly and do so with code instead of descriptions.
Second, when you say things like you can't use Pivot or Unpivot, you need to explain why because the very same thing preventing their use my also prevent the use of other things, such a CROSS APPLY, which I've used below. The code below also includes another method for creating readily consumable data.
--===== Create the test table and populate.
-- Most people that provide answers on this forum like to test
-- their solution prior to posting. With that in mind,
-- please do this in the future instead of just posting text.
-- See the first link in my signature line below for more.
-- This is not the solution. We're just defining the test data here.
SELECT *
INTO #TestData
FROM (VALUES
(1,120,60,40,80,120,120)
,(2,50 ,50,40,20,60 ,30)
,(3,50 ,25,40,10,90 ,30)
) v ([Month],A1,A2,B1,B2,C1,C2)
;
--===== Solve the problem using the ancient "Black Art" known as a CROSS TAB.
WITH
ctePreagg AS
(
SELECT v.X,td.[Month],Value = CONVERT(DECIMAL(9,3),Value)
FROM #TestData td
CROSS APPLY (VALUES
('A',(A1+0.0)/A2)
,('B',(B1+0.0)/B2)
,('C',(C1+0.0)/C2)
) v (X,Value)
)
SELECT X
,Jan = MAX(CASE WHEN [Month] = 1 THEN Value ELSE 0 END)
,Feb = MAX(CASE WHEN [Month] = 2 THEN Value ELSE 0 END)
,Mar = MAX(CASE WHEN [Month] = 3 THEN Value ELSE 0 END)
FROM ctePreAgg
GROUP BY X
ORDER BY X
;
--Jeff Moden
August 26, 2017 at 8:18 pm
Jeff, It's always fun to see the same question posted on 2 different forums... Looks like out two solutions are pretty similar... At least in terms of the "CROSS APPLY ( VALUES" unpivot & manual cross tab pivot...
http://forums.sqlteam.com/t/rows-to-columns-and-columns-to-rows-without-pivot/11267
August 27, 2017 at 12:51 pm
Jason A. Long - Saturday, August 26, 2017 8:18 PMJeff, It's always fun to see the same question posted on 2 different forums... Looks like out two solutions are pretty similar... At least in terms of the "CROSS APPLY ( VALUES" unpivot & manual cross tab pivot...
http://forums.sqlteam.com/t/rows-to-columns-and-columns-to-rows-without-pivot/11267
Great minds do think alike. 😉
I think it's a real shame that MS removed the CROSS TAB technique from BOL when they implemented that bloody crippled PIVOT function. For anyone interested in reading about it, here's an ages-old article I wrote about it and a second article on how to easily build dynamic CROSS TABs. The first article also includes a concept known as "Pre-Aggregation" (I give Peter Larsson the credit for coining that phrase), which makes the CROSS TAB method literally twice as fast as the equivalent PIVOT but still has more flexibility when it comes to calculating row and column totals.
http://qa.sqlservercentral.com/articles/T-SQL/63681/
http://qa.sqlservercentral.com/articles/Crosstab/65048/
I don't know who first came up with it but the first person I ever saw use CROSS APPLY as an UNPIVOT was Gianluca Sartori, the "Spaghetti DBA". Here's a link to his blog...
https://spaghettidba.com/
--Jeff Moden
August 27, 2017 at 2:26 pm
Yea... It's damn near painful seeing people trying to do complex pivots using the PIVOT operator when it's so simple using the old cross tab method.
As far as using the CROSS APPLY method for unpivoting data... IIRC the first time I saw it was in a Dwain Camps article.
August 28, 2017 at 11:55 am
arihantjain121189 - Saturday, August 26, 2017 10:19 AMAll I need is convert Column(A1/A2) into rows and rows(1 into Jan) into columns.Input:Here A1/A2 belongs to say A and they are calculated as A1/A2 for each month.
Month A1 A2 B1 B2 C1 C2 1 120 60 40 80 120 120 2 50 50 40 20 60 30 3 50 25 40 10 90 30 I need below o/p without using pivot and unpivot
O/P:
X Jan(1 is denoting Jan) Feb Mar A 120/60(calculation:A1/A2) 40/80 120/120 B 50/50 40/20 60/30 C 50/25 40/10 90/30 I tried but my query is too long as I am using case and Union All three times each For A1 A2,B1 B2,C1 C2 etc.
Can you pls help me out.
Thanks in advance
SELECT Node,
MAX(CASE WHEN month= 1 THEN Product ELSE NULL END) [Jan],
MAX(CASE WHEN month= 2 THEN Product ELSE NULL END) [Feb],
MAX(CASE WHEN month= 3 THEN Product ELSE NULL END) [Mar]
FROM
(
select 'A' NODE, month, A1*A2 Product
FROM table1
UNION ALL
select 'B' NODE, month, B1*B2 Product
FROM table1
UNION ALL
select 'C' NODE, month, C1*C2 Product
FROM table1
)
GROUP BY NODE;
August 29, 2017 at 9:21 pm
anand08sharma - Monday, August 28, 2017 11:55 AMarihantjain121189 - Saturday, August 26, 2017 10:19 AMAll I need is convert Column(A1/A2) into rows and rows(1 into Jan) into columns.Input:Here A1/A2 belongs to say A and they are calculated as A1/A2 for each month.
Month A1 A2 B1 B2 C1 C2 1 120 60 40 80 120 120 2 50 50 40 20 60 30 3 50 25 40 10 90 30 I need below o/p without using pivot and unpivot
O/P:
X Jan(1 is denoting Jan) Feb Mar A 120/60(calculation:A1/A2) 40/80 120/120 B 50/50 40/20 60/30 C 50/25 40/10 90/30 I tried but my query is too long as I am using case and Union All three times each For A1 A2,B1 B2,C1 C2 etc.
Can you pls help me out.
Thanks in advanceSELECT Node,
MAX(CASE WHEN month= 1 THEN Product ELSE NULL END) [Jan],
MAX(CASE WHEN month= 2 THEN Product ELSE NULL END) [Feb],
MAX(CASE WHEN month= 3 THEN Product ELSE NULL END) [Mar]
FROM
(
select 'A' NODE, month, A1*A2 Product
FROM table1
UNION ALL
select 'B' NODE, month, B1*B2 Product
FROM table1
UNION ALL
select 'C' NODE, month, C1*C2 Product
FROM table1
)
GROUP BY NODE;
That works but requires 3 scans of the table instead of just 1.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply