September 4, 2010 at 11:01 am
Actually, thank all you good folks. We've helped a lot of folks together over time.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2010 at 11:28 am
Jeff Moden (8/6/2010)
david.c.holley (8/6/2010)
I'd consider myself an advanced beginner. I started playing around with a table valued function mostly to see what would happen. I ended up creating one that uses a function to aggregate child records for a parent into columns by category - think initials of employees by department. I then created a view that joins projects to the resultant table giving me a list of projects with staff information as inCare to post the code, David?
1) I do not take credit for all of this as I referred to multiple articles, posts and other whatnot.
2) Keep in mind, I'd call myself an advanced beginner so if may not be entirely pristine in technique, but it works.
Here are the biggest elements of it. I'd have to go back and tweak things so that a full working example can be recreated. This should give you the general concepts. It breaks down into three steps
1) Transform multiple child records into a single comma-delimited string using a scalar function
In my implementation, these child records represent the initials of the staff assigned to one of 16 departments working an event.
2) Use the function above to create a cross-tab using a table function
This presents the individual departments as 16 individual columns whose value shows the initials of all staff assigned to the department for the show
3) Join the result of the table function with a table for a meaningful result
This function grabs the child records for a parent and converts them to a comma-delimited string. The test for Null was something unique to my implementation. (And yes, it should probably be COALESCE).
CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString]
(
@ShowNumber varchar(8), @ProductionDepartmentId integer
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @names VARCHAR(500)
SET @names = ''
SELECT
@names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
FROM
vw_ShowContacts
WHERE
ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
ORDER BY
Id
--Drop the last comma
IF Len(@names) > 0
BEGIN
SET @names = Right(@names,LEN(@names)-1)
End
RETURN @names
END
GO
This table function creates a cross-tab that lists the department contacts by department for the show as in. The second value is the primary key of the department in the departments table. If we had more departments, things might get ugly, but fortunately we'll only ever have the 16.
Show Depart1 Depart2 Depart4 Depart 5
5 AS DH, MM, TJ KL, MM
18 MH JA, RV
[Code]
CREATE FUNCTION [dbo].[GroupContactsByDepartment_InitialsOnly]
(
@ShowNumber varchar(8)
)
RETURNS TABLE
AS
RETURN
(
SELECT
@ShowNumber as ShowNumber,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 1) as Depart1,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 2) as Depart2,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 3) as Depart3,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 4) as Depart4,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 5) as Depart5,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 6) as Depart6,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 7) as Depart7,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 8) as Depart8,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 9) as Depart9,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 10) as Depart10,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 11) as Depart11,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 13) as Depart12,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 14) as Depart13,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 15) as Depart14,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 17) as Depart15,
dbo.getShowContactsForDepartment_AsString(@ShowNumber, 18) as Depart16
)
GO
[/Code]
Finally, this view takes the table function and joins it with the table that contains the specifics for the show turning the primary key of '5' into 'Annie' and other whatnot. I was quite surprised at how easy this was to implement once I knew the syntax. It did take some googling around to find it as the table-function examples that I found didn't demonstrate how to joing the results with another table or view.
[Code]
CREATE VIEW [dbo].[vw_ShowContacts_AllContactsByDepartmentInitialsOnly]
AS
SELECT
C.*
FROM
ShowHeaders as SH
OUTER APPLY GroupContactsByDepartment_InitialsOnly(SH.ShowNumber) AS C;
GO
[/Code]
September 5, 2010 at 8:00 pm
Jeff Moden (9/3/2010)
WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS 🙂
Thank you for the reference and for helping others, as well, Wayne.
Not a problem - I try to link to the best articles available out here, and your cross-tab/pivot articles are the best that I've seen.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 5, 2010 at 8:11 pm
David,
I was looking at your code you posted, and I have a couple of suggestions for you.
First, create the function as an in-line table-valued function (TVF). Not only is much faster this way, but you can JOIN to it.
Secondly - perhaps the fastest way to create a comma-delimited string is with the FOR XML clause.
So, putting both of these suggestions together, you would have:
CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString]
(
@ShowNumber varchar(8), @ProductionDepartmentId integer
)
WITH SCHEMABINDING -- get just a little bit more performance out of this
RETURNS TABLE
AS
SELECT ShowNumber = @ShowNumber,
ProductionDepartmentID = @ProductionDepartmentID,
STUFF((
SELECT
', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
FROM
vw_ShowContacts
WHERE
ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
ORDER BY
Id
FOR XML PATH('')),1,1,'')
GO
The FOR XML PATH('') returns a XML like ",AC,BC,AD", etc.
The STUFF function starts at the first character (the leading comma), and replaces one character with an empty string.
You might be interested in the article Using XML to Enhance the Performance of String Manipulations[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 5, 2010 at 9:51 pm
Thanks for the suggestions. The purpose of chasing the comma-delimited list of child records arose out of a practical need that's now a moot point (for now at least). The table function actually came out of playing around to see just what you could do with it.
September 6, 2010 at 10:41 am
WayneS (9/5/2010)
You might be interested in the article Using XML to Enhance the Performance of String Manipulations[/url]
There's one I missed... I'll check it out. Thanks, Wayne.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2010 at 2:30 pm
Just wanted to add another data point for crasstab performance vs pivot performance.
I'm creating an XML file in SSIS, there are 9 columns that are converted from rows in the table. The resulting table is around 200 meg, varying a bit day to day. The query results in around 143000 rows, again varying a bit from day to day.
I ran it originally with a PIVOT to get those 9 columns and SSIS says it took 4 minutes 58.165 seconds. I ran the same process with the crosstabs and SSIS says it took 47.861 seconds.
Crosstabs are over 6 times faster!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 2, 2010 at 12:08 am
Very cool. Thanks for the comparison, Stefan.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2011 at 12:08 pm
I tried to create a cross tab using the normal Cross Tab method for 10k rows, the query took over 4 minutes. No where near the hundreds of milliseconds in the test results.
-- CREATE TEST DATA
create table #main
(
Id INT IDENTITY
,[Description] VARCHAR(90)
)
create table #attributes
(
Id INT
,FieldName VARCHAR(128)
,FieldValue VARCHAR(MAX)
)
declare @i int
set @i = 1
declare @C int
declare @Id int
WHILE @i <= 10000
BEGIN
INSERT INTO #main(Description)
Values ('Description ' + right('00000' + convert(varchar(5), @i),5))
SET @Id = scope_identity()
set @C = 1
WHILE @C <= 183
BEGIN
INSERT INTO #attributes
(
Id
,FieldName
,FieldValue
)
VALUES
(
@Id
,'Field' + right('000' + convert(varchar(3), @C), 3)
,'FieldValue' + right('000' + convert(varchar(3), @C), 3) + 'Description' + right('00000' + convert(varchar(5), @i), 5)
)
END
set @i = @i + 1
END
SELECT
M.Id
,M.Description
,MIN(case when FieldName = 'Field001' then FieldValue END)
,MIN(case when FieldName = 'Field002' then FieldValue END)
,MIN(case when FieldName = 'Field003' then FieldValue END)
,MIN(case when FieldName = 'Field004' then FieldValue END)
,MIN(case when FieldName = 'Field005' then FieldValue END)
,MIN(case when FieldName = 'Field006' then FieldValue END)
,MIN(case when FieldName = 'Field007' then FieldValue END)
,MIN(case when FieldName = 'Field008' then FieldValue END)
,MIN(case when FieldName = 'Field009' then FieldValue END)
,MIN(case when FieldName = 'Field010' then FieldValue END)
,MIN(case when FieldName = 'Field011' then FieldValue END)
,MIN(case when FieldName = 'Field012' then FieldValue END)
,MIN(case when FieldName = 'Field013' then FieldValue END)
,MIN(case when FieldName = 'Field014' then FieldValue END)
,MIN(case when FieldName = 'Field015' then FieldValue END)
,MIN(case when FieldName = 'Field016' then FieldValue END)
,MIN(case when FieldName = 'Field017' then FieldValue END)
,MIN(case when FieldName = 'Field018' then FieldValue END)
,MIN(case when FieldName = 'Field019' then FieldValue END)
,MIN(case when FieldName = 'Field020' then FieldValue END)
,MIN(case when FieldName = 'Field021' then FieldValue END)
,MIN(case when FieldName = 'Field022' then FieldValue END)
,MIN(case when FieldName = 'Field023' then FieldValue END)
,MIN(case when FieldName = 'Field024' then FieldValue END)
,MIN(case when FieldName = 'Field025' then FieldValue END)
,MIN(case when FieldName = 'Field026' then FieldValue END)
,MIN(case when FieldName = 'Field027' then FieldValue END)
,MIN(case when FieldName = 'Field028' then FieldValue END)
,MIN(case when FieldName = 'Field029' then FieldValue END)
,MIN(case when FieldName = 'Field030' then FieldValue END)
,MIN(case when FieldName = 'Field031' then FieldValue END)
,MIN(case when FieldName = 'Field032' then FieldValue END)
,MIN(case when FieldName = 'Field033' then FieldValue END)
,MIN(case when FieldName = 'Field034' then FieldValue END)
,MIN(case when FieldName = 'Field035' then FieldValue END)
,MIN(case when FieldName = 'Field036' then FieldValue END)
,MIN(case when FieldName = 'Field037' then FieldValue END)
,MIN(case when FieldName = 'Field038' then FieldValue END)
,MIN(case when FieldName = 'Field039' then FieldValue END)
,MIN(case when FieldName = 'Field040' then FieldValue END)
,MIN(case when FieldName = 'Field041' then FieldValue END)
,MIN(case when FieldName = 'Field042' then FieldValue END)
,MIN(case when FieldName = 'Field043' then FieldValue END)
,MIN(case when FieldName = 'Field044' then FieldValue END)
,MIN(case when FieldName = 'Field045' then FieldValue END)
,MIN(case when FieldName = 'Field046' then FieldValue END)
,MIN(case when FieldName = 'Field047' then FieldValue END)
,MIN(case when FieldName = 'Field048' then FieldValue END)
,MIN(case when FieldName = 'Field049' then FieldValue END)
,MIN(case when FieldName = 'Field050' then FieldValue END)
,MIN(case when FieldName = 'Field051' then FieldValue END)
,MIN(case when FieldName = 'Field052' then FieldValue END)
,MIN(case when FieldName = 'Field053' then FieldValue END)
,MIN(case when FieldName = 'Field054' then FieldValue END)
,MIN(case when FieldName = 'Field055' then FieldValue END)
,MIN(case when FieldName = 'Field056' then FieldValue END)
,MIN(case when FieldName = 'Field057' then FieldValue END)
,MIN(case when FieldName = 'Field058' then FieldValue END)
,MIN(case when FieldName = 'Field059' then FieldValue END)
,MIN(case when FieldName = 'Field060' then FieldValue END)
,MIN(case when FieldName = 'Field061' then FieldValue END)
,MIN(case when FieldName = 'Field062' then FieldValue END)
,MIN(case when FieldName = 'Field063' then FieldValue END)
,MIN(case when FieldName = 'Field064' then FieldValue END)
,MIN(case when FieldName = 'Field065' then FieldValue END)
,MIN(case when FieldName = 'Field066' then FieldValue END)
,MIN(case when FieldName = 'Field067' then FieldValue END)
,MIN(case when FieldName = 'Field068' then FieldValue END)
,MIN(case when FieldName = 'Field069' then FieldValue END)
,MIN(case when FieldName = 'Field070' then FieldValue END)
,MIN(case when FieldName = 'Field071' then FieldValue END)
,MIN(case when FieldName = 'Field072' then FieldValue END)
,MIN(case when FieldName = 'Field073' then FieldValue END)
,MIN(case when FieldName = 'Field074' then FieldValue END)
,MIN(case when FieldName = 'Field075' then FieldValue END)
,MIN(case when FieldName = 'Field076' then FieldValue END)
,MIN(case when FieldName = 'Field077' then FieldValue END)
,MIN(case when FieldName = 'Field078' then FieldValue END)
,MIN(case when FieldName = 'Field079' then FieldValue END)
,MIN(case when FieldName = 'Field080' then FieldValue END)
,MIN(case when FieldName = 'Field081' then FieldValue END)
,MIN(case when FieldName = 'Field082' then FieldValue END)
,MIN(case when FieldName = 'Field083' then FieldValue END)
,MIN(case when FieldName = 'Field084' then FieldValue END)
,MIN(case when FieldName = 'Field085' then FieldValue END)
,MIN(case when FieldName = 'Field086' then FieldValue END)
,MIN(case when FieldName = 'Field087' then FieldValue END)
,MIN(case when FieldName = 'Field088' then FieldValue END)
,MIN(case when FieldName = 'Field089' then FieldValue END)
,MIN(case when FieldName = 'Field090' then FieldValue END)
,MIN(case when FieldName = 'Field091' then FieldValue END)
,MIN(case when FieldName = 'Field092' then FieldValue END)
,MIN(case when FieldName = 'Field093' then FieldValue END)
,MIN(case when FieldName = 'Field094' then FieldValue END)
,MIN(case when FieldName = 'Field095' then FieldValue END)
,MIN(case when FieldName = 'Field096' then FieldValue END)
,MIN(case when FieldName = 'Field097' then FieldValue END)
,MIN(case when FieldName = 'Field098' then FieldValue END)
,MIN(case when FieldName = 'Field099' then FieldValue END)
,MIN(case when FieldName = 'Field100' then FieldValue END)
,MIN(case when FieldName = 'Field101' then FieldValue END)
,MIN(case when FieldName = 'Field102' then FieldValue END)
,MIN(case when FieldName = 'Field103' then FieldValue END)
,MIN(case when FieldName = 'Field104' then FieldValue END)
,MIN(case when FieldName = 'Field105' then FieldValue END)
,MIN(case when FieldName = 'Field106' then FieldValue END)
,MIN(case when FieldName = 'Field107' then FieldValue END)
,MIN(case when FieldName = 'Field108' then FieldValue END)
,MIN(case when FieldName = 'Field109' then FieldValue END)
,MIN(case when FieldName = 'Field110' then FieldValue END)
,MIN(case when FieldName = 'Field111' then FieldValue END)
,MIN(case when FieldName = 'Field112' then FieldValue END)
,MIN(case when FieldName = 'Field113' then FieldValue END)
,MIN(case when FieldName = 'Field114' then FieldValue END)
,MIN(case when FieldName = 'Field115' then FieldValue END)
,MIN(case when FieldName = 'Field116' then FieldValue END)
,MIN(case when FieldName = 'Field117' then FieldValue END)
,MIN(case when FieldName = 'Field118' then FieldValue END)
,MIN(case when FieldName = 'Field119' then FieldValue END)
,MIN(case when FieldName = 'Field120' then FieldValue END)
,MIN(case when FieldName = 'Field121' then FieldValue END)
,MIN(case when FieldName = 'Field122' then FieldValue END)
,MIN(case when FieldName = 'Field123' then FieldValue END)
,MIN(case when FieldName = 'Field124' then FieldValue END)
,MIN(case when FieldName = 'Field125' then FieldValue END)
,MIN(case when FieldName = 'Field126' then FieldValue END)
,MIN(case when FieldName = 'Field127' then FieldValue END)
,MIN(case when FieldName = 'Field128' then FieldValue END)
,MIN(case when FieldName = 'Field129' then FieldValue END)
,MIN(case when FieldName = 'Field130' then FieldValue END)
,MIN(case when FieldName = 'Field131' then FieldValue END)
,MIN(case when FieldName = 'Field132' then FieldValue END)
,MIN(case when FieldName = 'Field133' then FieldValue END)
,MIN(case when FieldName = 'Field134' then FieldValue END)
,MIN(case when FieldName = 'Field135' then FieldValue END)
,MIN(case when FieldName = 'Field136' then FieldValue END)
,MIN(case when FieldName = 'Field137' then FieldValue END)
,MIN(case when FieldName = 'Field138' then FieldValue END)
,MIN(case when FieldName = 'Field139' then FieldValue END)
,MIN(case when FieldName = 'Field140' then FieldValue END)
,MIN(case when FieldName = 'Field141' then FieldValue END)
,MIN(case when FieldName = 'Field142' then FieldValue END)
,MIN(case when FieldName = 'Field143' then FieldValue END)
,MIN(case when FieldName = 'Field144' then FieldValue END)
,MIN(case when FieldName = 'Field145' then FieldValue END)
,MIN(case when FieldName = 'Field146' then FieldValue END)
,MIN(case when FieldName = 'Field147' then FieldValue END)
,MIN(case when FieldName = 'Field148' then FieldValue END)
,MIN(case when FieldName = 'Field149' then FieldValue END)
,MIN(case when FieldName = 'Field150' then FieldValue END)
,MIN(case when FieldName = 'Field151' then FieldValue END)
,MIN(case when FieldName = 'Field152' then FieldValue END)
,MIN(case when FieldName = 'Field153' then FieldValue END)
,MIN(case when FieldName = 'Field154' then FieldValue END)
,MIN(case when FieldName = 'Field155' then FieldValue END)
,MIN(case when FieldName = 'Field156' then FieldValue END)
,MIN(case when FieldName = 'Field157' then FieldValue END)
,MIN(case when FieldName = 'Field158' then FieldValue END)
,MIN(case when FieldName = 'Field159' then FieldValue END)
,MIN(case when FieldName = 'Field160' then FieldValue END)
,MIN(case when FieldName = 'Field161' then FieldValue END)
,MIN(case when FieldName = 'Field162' then FieldValue END)
,MIN(case when FieldName = 'Field163' then FieldValue END)
,MIN(case when FieldName = 'Field164' then FieldValue END)
,MIN(case when FieldName = 'Field165' then FieldValue END)
,MIN(case when FieldName = 'Field166' then FieldValue END)
,MIN(case when FieldName = 'Field167' then FieldValue END)
,MIN(case when FieldName = 'Field168' then FieldValue END)
,MIN(case when FieldName = 'Field169' then FieldValue END)
,MIN(case when FieldName = 'Field170' then FieldValue END)
,MIN(case when FieldName = 'Field171' then FieldValue END)
,MIN(case when FieldName = 'Field172' then FieldValue END)
,MIN(case when FieldName = 'Field173' then FieldValue END)
,MIN(case when FieldName = 'Field174' then FieldValue END)
,MIN(case when FieldName = 'Field175' then FieldValue END)
,MIN(case when FieldName = 'Field176' then FieldValue END)
,MIN(case when FieldName = 'Field177' then FieldValue END)
,MIN(case when FieldName = 'Field178' then FieldValue END)
,MIN(case when FieldName = 'Field179' then FieldValue END)
,MIN(case when FieldName = 'Field180' then FieldValue END)
,MIN(case when FieldName = 'Field181' then FieldValue END)
,MIN(case when FieldName = 'Field182' then FieldValue END)
,MIN(case when FieldName = 'Field183' then FieldValue END)
from
#main M
INNER JOIN
#attributes A
ON M.ID = A.ID
GROUP BY
M.Id
,m.Description
I tried using MAX also.
November 16, 2011 at 12:13 pm
mbova407 (11/16/2011)
I tried to create a cross tab using the normal Cross Tab method for 10k rows, the query took over 4 minutes. No where near the hundreds of milliseconds in the test results.I tried using MAX also.
Much of the performance can come down to your hardware, that's why it is important to try both methods on your machine and see which one gives you better performance. If the other method for the same test takes 10 minutes, then 4 minutes is pretty good.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 16, 2011 at 1:59 pm
But 4 minutes does not justify going vertical over horizontal 🙂
November 16, 2011 at 2:12 pm
mbova407 (11/16/2011)
But 4 minutes does not justify going vertical over horizontal 🙂
I'd say it does if horizontal is 10 minutes or more.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 17, 2011 at 4:47 pm
mbova407 (11/16/2011)
I tried to create a cross tab using the normal Cross Tab method for 10k rows, the query took over 4 minutes. No where near the hundreds of milliseconds in the test results.{snip} ....
I tried using MAX also.
You're also pivoting 183 columns compared to the very few from the article. That's going to make a hefty difference no matter which method you have and no matter which hardware you have.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2017 at 11:16 pm
I've come across this article and a few others that suggest it is a limitation of PIVOT that you can't PIVOT more than one column. I am not sure I understand the need for creating two pivots and joining them. I would just alias the column we are pivoting on for each PIVOT clause.
SELECT [Company],
[Year],
[1_Qty]=MAX([1_Qty]),
[2_Qty]=MAX([2_Qty]),
[3_Qty]=MAX([3_Qty]),
[4_Qty]=MAX([4_Qty]),
[1_Amt]=MAX([1_Amt]),
[2_Amt]=MAX([2_Amt]),
[3_Amt]=MAX([3_Amt]),
[4_Amt]=MAX([4_Amt])
FROM
(
SELECT [Company],
Year,
[Qtr_Qty]=CONVERT( CHAR(1), Quarter)+'_Qty',
[Qtr_Amt]=CONVERT( CHAR(1), Quarter)+'_Amt',
[Quantity],
[Amount]
FROM @testtable
) [pt] PIVOT(SUM([Quantity]) FOR [Qtr_Qty] IN([1_Qty],
[2_Qty],
[3_Qty],
[4_Qty]))
AS [qty] PIVOT(SUM([Amount]) FOR [Qtr_Amt] IN([1_Amt],
[2_Amt],
[3_Amt],
[4_Amt]))
AS [amt]
GROUP BY [Company],
[Year];
October 2, 2017 at 6:00 pm
david.leyden - Sunday, October 1, 2017 11:16 PMI've come across this article and a few others that suggest it is a limitation of PIVOT that you can't PIVOT more than one column. I am not sure I understand the need for creating two pivots and joining them. I would just alias the column we are pivoting on for each PIVOT clause.
SELECT [Company],
[Year],
[1_Qty]=MAX([1_Qty]),
[2_Qty]=MAX([2_Qty]),
[3_Qty]=MAX([3_Qty]),
[4_Qty]=MAX([4_Qty]),
[1_Amt]=MAX([1_Amt]),
[2_Amt]=MAX([2_Amt]),
[3_Amt]=MAX([3_Amt]),
[4_Amt]=MAX([4_Amt])
FROM
(
SELECT [Company],
Year,
[Qtr_Qty]=CONVERT( CHAR(1), Quarter)+'_Qty',
[Qtr_Amt]=CONVERT( CHAR(1), Quarter)+'_Amt',
[Quantity],
[Amount]
FROM @testtable
) [pt] PIVOT(SUM([Quantity]) FOR [Qtr_Qty] IN([1_Qty],
[2_Qty],
[3_Qty],
[4_Qty]))
AS [qty] PIVOT(SUM([Amount]) FOR [Qtr_Amt] IN([1_Amt],
[2_Amt],
[3_Amt],
[4_Amt]))
AS [amt]
GROUP BY [Company],
[Year];
Thanks... I just learned something new.
EDIT: With further experimentation, I've discovered that the method above doesn't calculate the totals correctly! Please see the proof a couple of posts down from this one!
DON'T USE THE METHOD POSTED ABOVE BECAUSE IT DOESN'T WORK CORRECTLY AND IT BREAKS IN ALL VERSIONS OF SQL SERVER THRU 2016!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 226 through 240 (of 243 total)
You must be logged in to reply to this topic. Login to reply