How to do math job with this two table?

Question

Post reply

How to do math job with this two table?

adonetok

SSCrazy Eights

Points: 8395
More actions
June 8, 2012 at 2:56 pm

#392466

There are two tables A and B as below.
TABLE A:
NAME201202201203201204
BBB------37038---39188----23172
AAA-----34204---41337----21618
TABLE B:
Name201202201203201204
BBB-----5-------6--------7
AAA-----3-------9--------4
How to do math job with this two table for project's automation?
I need result like below
A.201202/B.201202 AS 201202AVERAGE
A.201203/B.201203 AS 201203AVERAGE
A.201204/B.201204 AS 201204AVERAGE
Since these two tables are created using crosstab query, column's name will change dynamacly.
For example, next month column's name will change to 201203,201204,201205

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

With dynamic sql.

If you want some real help post some ddl, sample data and desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vinu512 SSCoach Points: 15729 More actions · Answer 2

You could use unpivot in this case as follows:

--Creating Tables

Create Table A

(NAME varchar(3),

[201202] int,

[201203] int,

[201204] int )

Create Table B

(NAME varchar(3),

[201202] int,

[201203] int,

[201204] int )

--Inserting Sample Data

Insert into A

Select 'BBB', 37038, 39188, 23172

Union ALL

Select 'AAA', 34204, 41337, 21618

Insert Into B

Select 'BBB', 5, 6, 7

Union ALL

Select 'AAA', 3, 9, 4

--Static UnPivot Type1

Select NAME, Years, Average From

(Select A.NAME, (A.[201202]/B.[201202]) As [201202], (A.[201203]/B.[201203]) As [201203], (A.[201204]/B.[201204]) As [201204] From A

Join B ON A.NAME = B.NAME) As p

Unpivot

(Average For Years IN ([201202], [201203], [201204])) As u

Order By NAME

--Static UnPivot Type2

Select x.NAME, x.Years, (x.Value/y.Value) As Average From

(Select NAME, Years, Value From

(Select NAME, [201202], [201203], [201204] From A) As p

Unpivot

(Value For Years In ([201202], [201203], [201204])) As u) As x

JOIN

(Select NAME, Years, Value From

(Select NAME, [201202], [201203], [201204] From B) As p

Unpivot

(Value For Years In ([201202], [201203], [201204])) As u) As y ON x.NAME = y.NAME AND x.Years = y.Years

Order By x.NAME

--Dynamic UnPivot

Declare @cols varchar(max), @sql varchar(max)

Declare @temp Table(Cols varchar(max))

Insert Into @temp

Select Distinct x.Years From

(Select NAME, Years, Value From

(Select NAME, [201202], [201203], [201204] From A) As p

Unpivot

(Value For Years In ([201202], [201203], [201204])) As u) As x

JOIN

(Select NAME, Years, Value From

(Select NAME, [201202], [201203], [201204] From B) As p

Unpivot

(Value For Years In ([201202], [201203], [201204])) As u) As y ON x.NAME = y.NAME AND x.Years = y.Years

Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp

Set @sql = 'Select x.NAME, x.Years, (x.Value/y.Value) As Average From

(Select NAME, Years, Value From

(Select NAME, '+@cols+' From A) As p

Unpivot

(Value For Years In ('+@cols+')) As u) As x

JOIN

(Select NAME, Years, Value From

(Select NAME, '+@cols+' From B) As p

Unpivot

(Value For Years In ('+@cols+')) As u) As y ON x.NAME = y.NAME AND x.Years = y.Years

Order By x.NAME'

Execute (@sql)

You could do the Static Unpivot in two ways as shown in the above code. You could either JOIN the tables and then Unpivot the result set or you can Unpivot the two tables and then do a JOIN.

In the Static sense I think that the "Static Unpivot type1" is easier.

But, while applying it Dynamically I found that "Static Unpivot Type2" was easier to apply Dynamically.

Hope this was what you were looking for.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

adonetok (6/8/2012)
Since these two tables are created using crosstab query, column's name will change dynamacly.
For example, next month column's name will change to 201203,201204,201205

The key here is that you're trying to do a calculation AFTER it has been formatted for display using the CROSS TAB. Do the Average calculation BEFORE you do the CROSS TAB.

--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)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

Jeff is right, but if you can't change to do what he's saying, then you can also do it this way.

CREATE TABLE #TABLEA (Name VARCHAR(3), [201202] INT, [201203] INT, [201204] INT)

CREATE TABLE #TABLEB (Name VARCHAR(3), [201202] INT, [201203] INT, [201204] INT)

INSERT INTO #TABLEA

SELECT 'BBB', 37038, 39188, 23172

UNION ALL SELECT 'AAA', 34204, 41337, 21618

INSERT INTO #TABLEB

SELECT 'BBB', 5, 6, 7

UNION ALL SELECT 'AAA', 3, 9, 4

-- Static UNPIVOT without using UNPIVOT

SELECT a.Name, Date, Avg

FROM #TABLEA a

INNER JOIN #TABLEB b ON a.Name = b.Name

CROSS APPLY (

SELECT 1. * a.[201202] / b.[201202], 1. * a.[201203] / b.[201203], 1. * a.[201204] / b.[201204]

) x([201202], [201203], [201204])

CROSS APPLY (

VALUES ('[201202]', x.[201202]), ('[201203]', x.[201203]), ('[201204]',x.[201204])) y (Date, Avg)

DECLARE @sql VARCHAR(MAX)

-- Dynamic UNPIVOT with date column names taken from Dates CTE

;WITH Dates (d) AS (SELECT '[201202]' UNION ALL SELECT '[201203]' UNION ALL SELECT '[201204]')

SELECT @sql = '

SELECT a.Name, Date, Avg FROM #TABLEA a INNER JOIN #TABLEB b ON a.Name = b.Name

CROSS APPLY (

SELECT ' +

STUFF(

(SELECT ',1.*a.' + d + '/b.' + d

FROM Dates

FOR XML PATH(''), root('MyString'), type

).value('/MyString[1]','varchar(max)' )

, 1, 1, '') + ')x(' +

STUFF(

(SELECT ',' + d

FROM Dates

FOR XML PATH(''), root('MyString'), type

).value('/MyString[1]','varchar(max)' )

, 1, 1, '') + ') CROSS APPLY ( VALUES ' +

STUFF(

(SELECT ',(''' + d + ''', x.' + d + ')'

FROM Dates

FOR XML PATH(''), root('MyString'), type

).value('/MyString[1]','varchar(max)' )

, 1, 1, '') + ') y (Date, Avg)'

--SELECT @sql

EXEC (@SQL)

DROP TABLE #TABLEA, #TABLEB

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St