October 19, 2007 at 11:59 am
I have a result returned from a query that is the result of a join. I need to make the results rows to columns.
Here is my example:
Select OrderDate, OrderQty, ProductNo from view1
Result:
OrderDate --- OrderQty --- ProductNo
1/1/2007--------13-----------1000345
1/1/2007--------19-----------1000355
1/1/2007--------93-----------1000666
1/2/2007--------47-----------1000345
1/2/2007--------99-----------1000355
1/2/2007--------93-----------1000666
What I need from this data is this:
OrderDate---1000345---1000355---1000666
1/1/2007--------13-----------19-----------93
1/2/2007--------47-----------99-----------33
etc...
Thanks in Advance.
October 19, 2007 at 12:18 pm
Here is a Jeff MOden example I keep in my toolbox
Set NOCount On
---===== If the temp table to hold the results in exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
---===== Populate the temp table with results from your original query
select [field to be rows] as ciname,[filed to be columns] as attributename,sum([field to be aggregated]) as attributevalue
INTO #Results
from [your table/view]
IF @@RowCount = 0
Return
CREATE
INDEX [test] ON #Results ([AttributeName])
--===== Declare some local variables to hold some Dynamic SQL
DECLARE
@MySQL1 VARCHAR(8000)
DECLARE
@MySQL2 VARCHAR(8000)
DECLARE
@MySQL3 VARCHAR(8000)
--===== Build the SELECT clause
SET @MySQL1 = 'SELECT ciName,'
--===== Build the select LIST (do not try to reformat or you'll mess it up!)
SELECT @MySQL2 = ISNULL(@MySQL2 + ',','') + '
SUM(CASE WHEN AttributeName = ''' + AttributeName + '''
THEN AttributeValue ELSE NULL END) AS ' + '[' + AttributeName + ']'
FROM (SELECT DISTINCT TOP 100 PERCENT AttributeName AS AttributeName
FROM #Results
Order by AttributeName) d
--===== Build the FROM and GROUP BY clauses
SELECT @MySQL3 = CHAR(13) + 'FROM #Results GROUP BY ciName'
--===== Display the resulting SQL (you can take this piece out, just for demo)
--if @Debug = 1
PRINT @MySQL1+@MySQL2+@MySQL3
--===== Execute the Dynamic SQL
EXEC ( @MySQL1 +@MySQL2 +@MySQL3 )
October 19, 2007 at 12:31 pm
Please allow me to add something else to this...
The columns will not change. This is not an example where the productno is changing. I have 3 Productno(s) and will never have any more, so the temp table can be statically built. The part I am stuck on is a query that will populate this table with the appropriate rows (inverted) as the number of rows will change (obviously).
Thanks for all of the quick responses so far.
October 19, 2007 at 12:40 pm
That is easier.
select orderdate,
sum(case when productno = 1000345 then orderqty else 0 end) as [1000345],
sum(case when productno = 1000355 then orderqty else 0 end) as [1000355],
sum(case when productno = 1000666 then orderqty else 0 end) as [1000666]
group by orderdate
This will sum all of the order quantites for a given day and product number.
October 19, 2007 at 2:30 pm
We got it all worked out. Thanks to everyone who replied to this post.
October 20, 2007 at 12:01 am
Here is a Jeff Moden example I keep in my toolbox
Heh... thank you kindly for the "plug" 😀 Got a present for ya, MrPoleCat...
CREATE PROCEDURE dbo.AutoCrossTab
/**************************************************************************
Purpose:
-------
Given data in a 3 column external temp table, "auto-magically" create a
pivot report for that data.
Inputs:
-------
@pRowName:
Optional - Defaults to 'Row Name' and will appear down the left side
of the report in the first column.
@pTotals
Optional - Defaults to 0
0 = No totals
1 = Row totals displayed in last column on right
2 = Column totals displayed in last row at bottom
3 = Both sets of totals displayed
@pDebug
Optional - Defaults to 0
0 = Pivot report will be output
1 = SQL that creates the pivot report will be output
Outputs:
--------
Pivot report
Left Column - Created from RNam column of the existing #Results table.
Column Names - Totals will be named 'Total' for rows and columns.
- Left most column name defaults to 'Row Name' or may be
assigned by the @pRowName parameter.
- All other column names dervied from CNam column of the
existing #Results table.
Content - Sum aggragated CVal column from the existing #Results
table.
Revision History:
Rev 00 - Date Unk - Jeff Moden - Initial creation
Rev 01 - 10/20/2007 - Jeff Moden - Converted to stored procedure and
extra functionality added through
parameters explained above.
**************************************************************************/
--===== Declare the I/0 parameters
@pRowName VARCHAR(128) = 'Row Name',
@pTotals INTEGER = 0,
@pDebug INTEGER = 0
AS
--===== Setup the environment
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--===== Declare some local variables to hold some Dynamic SQL
DECLARE @MySQL1 VARCHAR(8000) --SELECT and Row Name
DECLARE @MySQL2 VARCHAR(8000) --Select LIST
DECLARE @MySQL3 VARCHAR(8000) --Optional Row Totals
DECLARE @MySQL4 VARCHAR(8000) --FROM, GROUP BY, and optional column totals
--===== Build the SELECT clause
SET @MySQL1 =
CASE
WHEN @pTotals IN (2,3)
THEN 'SELECT CASE WHEN GROUPING(RNam) = 0 '
+ 'THEN CAST(RNam AS VARCHAR(128)) '
+ 'ELSE ''Total'' END [' + @pRowName + '],' + CHAR(13)
ELSE 'SELECT RNam[' + @pRowName + '],' + CHAR(13)
END
--===== Build the select LIST
SELECT @MySQL2 =
ISNULL(@MySQL2 + ','+CHAR(13),'') +
+ 'SUM(CASE WHEN CNam=''' + CNam + ''''
+ 'THEN CVal ELSE 0 END)' + '[' + CNam + ']'
FROM (--==== Derived table forces order of column names
SELECT DISTINCT TOP 100 PERCENT CNam AS CNam
FROM #Results
ORDER BY CNam) d
--===== If totals are turned on, calculate the row total
SELECT @MySQL3 =
CASE
WHEN @pTotals IN (1,3)
THEN CHAR(13) + ',SUM(CVal) AS Total'
ELSE ''
END
--===== Build the FROM and GROUP BY clauses
-- If the totals are turned on, calculate column totals
SELECT @MySQL4 =
CHAR(13) + 'FROM #Results GROUP BY RNam'
+ CASE
WHEN @pTotals IN (2,3)
THEN ' WITH ROLLUP'
ELSE ''
END
--===== If debug mode is on, just print the dynamic SQL...
-- Otherwise, execute the dynamic SQL
IF @pDebug = 1
PRINT @MySQL1+@MySQL2+@MySQL3+@MySql4
ELSE EXEC (@MySQL1+@MySQL2+@MySQL3+@MySql4)
GO
Note that the #Results table must exist prior to calling the proc above... here's the general format of the calling code...
--===== Setup the environment
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--===== If the temp table to hold the #Results in exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
--===== Populate the temp table with #Results from your original query... for example...
SELECT [field to be rows] AS RNam, --<<< Column alias must be this
[field to be columns] AS CNam, --<<< Column alias must be this
SUM([field to be aggregated]) AS CVal --<<< Column alias must be this
INTO #Results --<<< Table name must be this!!!
FROM [your table/view]
GROUP BY [field to be rows], [field to be columns]
--===== Create an index for a little extra speed
CREATE CLUSTERED INDEX IDX_#Results_RNam ON #Results (RNam)
--===== Create either the Pivot table report or...
-- build the SQL that does so it can be "customized".
-- Parameters are for example... read the header of the proc for details
EXEC dbo.AutoCrossTab @pRowName = '2 Letter Account',
@pTotals = 3, --Print row and column totals
@pDebug = 0 --Display the report, not the dynamic SQL
I trimmed down the dynamic SQL as much as possible to handle 10 years of months named yyyy-mm and the totals.
Seriously, read the infomation in the header of the proc... I don't think folks are ever going to have to write code for a crosstab pivot ever again 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2007 at 1:01 pm
I will start with "VERY COOL". 😎
One small fix I had to make to the set up is to switch the from and group by. I'd paste it here but it would be better if you edit your post for the next guy who uses it.
I tested my cursor version against this on a million rows ((your code with a few limitations on the data) with 365 dates for rows and 26 letters for columns, summing ints and yours is more than twice as fast. 20 seconds vs. 55 seconds. I think this is more due to the clustered index you are putting on the #Result table than it is on the cursor itself. I am going to see what better indexing on the original table does to narrow the gap.
One caveat is that my cursor version allows for more than one column in the rows and can handle the aggregate passed as a parameter.
October 22, 2007 at 11:17 pm
Jeff,
If I was really mean I would make RAC open source 🙂
You could try to duplicate some functionality. Who knows you may come up with some good techniques. Then you will be in the ironic position of having to thank me for the motivation 🙂
best,
October 22, 2007 at 11:22 pm
If making RAC open source would stop your spamming, I'd be happy about that. 😛 And, your insults are a great way to drum up business :w00t:
Just curious... how long does it take RAC to do the million row test to accomplish the same thing? I'm not talking about development time, I'm talking about execution time.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2007 at 12:08 am
Fixed that... thanks for the observation... I always hate coverting working code into instructional code... I mess it up almost every time :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2007 at 12:20 am
Jeff, excellent code but I think you should start using QUOTENAME function for those having brackets, spaces or even single quotes in their table/column names.
N 56°04'39.16"
E 12°55'05.25"
October 23, 2007 at 12:43 am
Peter Larsson (10/23/2007)
Jeff, excellent code but I think you should start using QUOTENAME function for those having brackets, spaces or even single quotes in their table/column names.
It doesn't make any difference for spaces or single quotes (in this case) but for square brackets - spot on.
_____________
Code for TallyGenerator
October 23, 2007 at 1:00 am
Hey Jeff :),
Great Standard of Code,
I really feel its the way need to be done.
but i have a small issue here,
when i am trying to copy your code to my SQL editor its
mess up all the code,
How can i copy the your code in systematic way?
I mean the way it present here.
Cheers!
Sandy.
--
October 23, 2007 at 1:38 am
Hey Jason Tontz,
In SQL 2005,
you can use this Query.
SQL SCRIPT
=========
SELECT
OrdProductPivot.orderdate,
[1000345] = ISNULL([1000345], 0),
[1000355] = ISNULL([1000355], 0),
[1000666] = ISNULL([1000666], 0)
FROM Ord_Product
PIVOT (max(orderqty) FOR productno IN ([1000345], [1000355], [1000666])
) AS OrdProductPivot
---------------------------
Cheers!
Sandy
--
October 23, 2007 at 1:42 am
Just curious... how long does it take RAC to do the million row test to accomplish the same thing? I'm not talking about development time, I'm talking about execution time.[/quote]
Hello Jeff,
RAC will never be amongst the fastest solutions. It is a simple tradoff, performance for functionality. RAC does not build a single SELECT statement to execute against a summary table. It would be virtually impossible to design a system (in t-sql) that could do everything RAC can do in a single framework and with a single pass thru the data /summary table where the end result is encapsulated in a single SELECT. But if that 'could' be done then it would be considerably faster. As a developer you understand the delicate balance between performance, functionality and stability. And you know there are no free lunches 🙂
best,
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply