September 14, 2016 at 9:13 am
Jason A. Long (9/14/2016)
drew.allen (9/14/2016)
The phrase is "moot point".Fixed. 😀
Just looking at the execution plans... Summing 1st is quite a bit more efficient...
:pinch: Memory failure :pinch:
The cross apply will produce Number of Pivot Columns x Initial Rows, it will therefore always be more expensive. Thinking about it, the pre-pivot most likely will get a hash aggregate while pre-aggregate will certainly get a stream aggregate in the execution plan, yet another reason why the pre-pivot will be slower.
😎
September 14, 2016 at 9:16 am
The Dixie Flatline (9/14/2016)
Is it just me, or is the syntax for PIVOT/UNPIVOT *much* harder to follow than cross-tab or APPLY VALUES ?
Not only is it more contorted but also it has more limitations and is somewhat less efficient.
😎
September 14, 2016 at 9:26 am
How about this.....
SELECT 'AZ' [STATE], SUM(AZ) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'NY' [STATE], SUM(NY) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'PA' [STATE], SUM(PA) [VAL] FROM EXAMPLE
September 14, 2016 at 9:33 am
The Dixie Flatline (9/14/2016)
Is it just me, or is the syntax for PIVOT/UNPIVOT *much* harder to follow than cross-tab or APPLY VALUES ?
Agree 100%... PIVOT & UNPIVOT were solutions to nonexistent problems (IMO).
September 14, 2016 at 9:37 am
tripleAxe (9/14/2016)
How about this.....
SELECT 'AZ' [STATE], SUM(AZ) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'NY' [STATE], SUM(NY) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'PA' [STATE], SUM(PA) [VAL] FROM EXAMPLE
That's going to do 3 separate pulls from the base table, making it far less efficient than either of the CROSS APPLY methods.
September 14, 2016 at 9:38 am
Thanks a lot guys. However I tried this approach
CREATE TABLE #UnPivotExample(code varchar(2), value int)
INSERT INTO #UnPivotExample (code,value)
SELECT code,value
FROM
(SELECT [AZ], [NY], [PA] FROM #Example
) AS cp
UNPIVOT
(value for code IN ([AZ], [NY], [PA])
) AS up
SELECT * FROM #UnPivotExample
Now one more challange of the business rules is, I cant use original Example Table.
And assume I only have #UnPivotExample table. How do I convert it back to Example table using PIVOT or other easier way?
September 14, 2016 at 9:42 am
INSERT INTO #EXAMPLE(AZ,NY,PA)
SELECT AZ,NY,PA FROM
(SELECT code,value FROM #UnPivotExample
) AS cp
PIVOT
( MAX(code) for value IN ([AZ],[NY],[PA])
) AS up
When I try this way, it says,
Msg 8114, Level 16, State 1, Line 50
Error converting data type nvarchar to int.
Msg 473, Level 16, State 1, Line 50
The incorrect value "AZ" is supplied in the PIVOT operator.
September 14, 2016 at 9:46 am
dallas13 (9/14/2016)
Thanks a lot guys. However I tried this approach
CREATE TABLE #UnPivotExample(code varchar(2), value int)INSERT INTO #UnPivotExample (code,value)
SELECT code,value
FROM
(SELECT [AZ], [NY], [PA] FROM #Example
) AS cp
UNPIVOT
(value for code IN ([AZ], [NY], [PA])
) AS up
SELECT * FROM #UnPivotExample
Now one more challange of the business rules is, I cant use original Example Table.
And assume I only have #UnPivotExample table. How do I convert it back to Example table using PIVOT or other easier way?
A better table design would be the right thing to do. The data should normally be stored as normalized vertical table with the state names in a column. Then you don't have to juggle so hard and you actually stand a chance of DRI enforcement. It will make aggregations and reporting much easier, as well because you won't have to play the dissect/rebuild game every time you need to do something with the data. You would also be able to "date" the entries providing even more reporting utility. After that, CROSS TAB reporting becomes nasty fast and a computational breeze to code.
Also, your code doesn't produce the result set you originally asked for, either. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2016 at 9:50 am
You can't pivot it back unless you preserve the original ID. See below.
CREATE TABLE #UnPivotExample(code varchar(2), [value] int, id int)
INSERT INTO #UnPivotExample (code,value,id)
SELECT code,value,id
FROM
(SELECT ID, [AZ], [NY], [PA] FROM #Example
) AS cp
UNPIVOT
(value for code IN ([AZ], [NY], [PA])
) AS up
SELECT * FROM #UnPivotExample
select * from #example
-- cross-tab (old school PIVOT)
select ID, sum(Case when Code = 'AZ' then value else null end) as AZ
, sum(Case when Code = 'NY' then value else null end) as NY
, sum(Case when Code = 'PA' then value else null end) as PA
from #UnPivotExample
group by ID
order by ID
I will anticipate your next question, which is: How do you dynamically make this work any other states, if they are in the example data? The only way I know to do this is by using dynamic SQL to find the list of states coming in and to write a query string that includes all of them, then execute it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2016 at 11:43 am
Once again Thanks a lot.
Jeff- I understand your concern but we have data coming from third party so we have to massage data here.
The Dixie Flatline- You are right. I have id column, just I forgot to put in my example.
We will be dealing with hundreds of columns here. The data we have has column name = state names (doesn't make sense) so we tried to convert all columns to rows and put them in transaction table. So we have 2 sets of data and we dont want to use both because in future if any updates\modification then we will make update at one place only to avoid data corruption. But to create various reports now the challenge is to again convert all vertical values to horizontal.And for that we have to write so many complicated, expensive queries I guess..Once again Thanks a lot.
September 14, 2016 at 12:03 pm
dallas13 (9/14/2016)
Once again Thanks a lot.Jeff- I understand your concern but we have data coming from third party so we have to massage data here.
The Dixie Flatline- You are right. I have id column, just I forgot to put in my example.
We will be dealing with hundreds of columns here. The data we have has column name = state names (doesn't make sense) so we tried to convert all columns to rows and put them in transaction table. So we have 2 sets of data and we dont want to use both because in future if any updates\modification then we will make update at one place only to avoid data corruption. But to create various reports now the challenge is to again convert all vertical values to horizontal.And for that we have to write so many complicated, expensive queries I guess..Once again Thanks a lot.
Not complicated. Not expensive. Not slow. See the following. Keep in mind that both articles are introductions to the art. The second article shows you how to get the computer to write the reports for you.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2016 at 12:20 pm
tripleAxe (9/14/2016)
How about this.....
SELECT 'AZ' [STATE], SUM(AZ) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'NY' [STATE], SUM(NY) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'PA' [STATE], SUM(PA) [VAL] FROM EXAMPLE
Yes, this was my question from the beginning - until I hit your exact same observation.
So ... why not use this apparently much simpler construction instead of the less-obvious CROSS APPLY ?
September 14, 2016 at 12:53 pm
j-1064772 (9/14/2016)
tripleAxe (9/14/2016)
How about this.....
SELECT 'AZ' [STATE], SUM(AZ) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'NY' [STATE], SUM(NY) [VAL] FROM EXAMPLE
UNION ALL
SELECT 'PA' [STATE], SUM(PA) [VAL] FROM EXAMPLE
Yes, this was my question from the beginning - until I hit your exact same observation.
So ... why not use this apparently much simpler construction instead of the less-obvious CROSS APPLY ?
Because it's ~3X more expensive... Don't make 3 separate pulls from the source table when you can get it in 1.
September 14, 2016 at 12:59 pm
Because it scans the input data start to finish three times. Once for each total.
The more states you add, the more table scans it is going to do.
For the three rows of your example it doesn't matter, the speed difference is negligible, but don't expect it to scale well at large volumes.
Note: The SSC culture tends to look at how well a query will perform against a million rows. Code is judged by its efficiency, not by its intuitiveness . If you can accept that you will learn some really powerful techniques here. Even after years here, I learned a new one just today.
But if you *REALLY* love that format, you could do this to make it run faster.
if OBJECT_ID(N'tempdb..#sums') is not null d rop table #sums
-- make one pass through the input table to get sums for all states
select sum(AZ) as AZ, sum(NY) as NY, sum(PA) as PA
into #sums
from #example
-- still reading three times, just re-reading a single row
-- instead of potentially hundreds of thousands of rows.
select 'AZ' as ST, AZ as val from #sums
union all
select 'NY' as ST, NY as val from #sums
union all
select 'PA' as ST, PA as val from #sums
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2016 at 1:25 pm
Clear explanation, thank you all.
Still not in the mindset of a million rows ... My DB's are considerably more modest.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply