June 22, 2008 at 8:37 pm
As some of you may know, I've only been working with SQL Server 2005 for about 6 months now. I know how to do the following (see desired results below) using a Cross Tab and I'm pretty sure it CANNOT be accomplished as a single Pivot. I haven't tried it using two Pivots in derived tables but I think THAT can be done. I'm just pretty sure it can't be done in a single PIVOT and want to make sure... no better place than here...
Practicing what I preach, here's the data... it's pretty straight forward but, basically, it contains 3 years of quarterly amounts and quantities for 2 different companies. If there were a PK, it would be on a composite of Company, Year, and Quarter...
CREATE TABLE #SomeTable
(
Company VARCHAR(3),
Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1),
Quantity DECIMAL(2,1))
GO
INSERT INTO #SomeTable
(Company,Year, Quarter, Amount, Quantity)
SELECT 'ABC', 2006, 1, 1.1, 2.2 UNION ALL
SELECT 'ABC', 2006, 2, 1.2, 2.4 UNION ALL
SELECT 'ABC', 2006, 3, 1.3, 1.3 UNION ALL
SELECT 'ABC', 2006, 4, 1.4, 4.2 UNION ALL
SELECT 'ABC', 2007, 1, 2.1, 2.3 UNION ALL
SELECT 'ABC', 2007, 2, 2.2, 3.1 UNION ALL
SELECT 'ABC', 2007, 3, 2.3, 2.1 UNION ALL
SELECT 'ABC', 2007, 4, 2.4, 1.5 UNION ALL
SELECT 'ABC', 2008, 1, 1.5, 5.1 UNION ALL
SELECT 'ABC', 2008, 3, 2.3, 3.3 UNION ALL
SELECT 'ABC', 2008, 4, 1.9, 4.2 UNION ALL
SELECT 'XYZ', 2006, 1, 2.1, 3.6 UNION ALL
SELECT 'XYZ', 2006, 2, 2.2, 1.8 UNION ALL
SELECT 'XYZ', 2006, 3, 3.3, 2.6 UNION ALL
SELECT 'XYZ', 2006, 4, 2.4, 3.7 UNION ALL
SELECT 'XYZ', 2007, 1, 3.1, 1.9 UNION ALL
SELECT 'XYZ', 2007, 2, 1.2, 1.2 UNION ALL
SELECT 'XYZ', 2007, 3, 3.3, 4.2 UNION ALL
SELECT 'XYZ', 2007, 4, 1.4, 4.0 UNION ALL
SELECT 'XYZ', 2008, 1, 2.5, 3.9 UNION ALL
SELECT 'XYZ', 2008, 2, 3.5, 2.1 UNION ALL
SELECT 'XYZ', 2008, 3, 1.3, 3.9 UNION ALL
SELECT 'XYZ', 2008, 4, 3.9, 3.4
GO
... and here's what the results should look like... again, I know how to do this using a Cross Tab, no problem... I'm trying to find out if there's a way to do it using a single Pivot.
Company Year Q1 Amt Q1 Qty Q2 Amt Q2 Qty Q3 Amt Q3 Qty Q4 Amt Q4 Qty Total Amt Total Qty
------- ------ ------ ------ ------ ------ ------ ------ ------ ------ --------- ---------
ABC 2006 1.1 2.2 1.2 2.4 1.3 1.3 1.4 4.2 5.0 10.1
ABC 2007 2.1 2.3 2.2 3.1 2.3 2.1 2.4 1.5 9.0 9.0
ABC 2008 1.5 5.1 0.0 0.0 2.3 3.3 1.9 4.2 5.7 12.6
XYZ 2006 2.1 3.6 2.2 1.8 3.3 2.6 2.4 3.7 10.0 11.7
XYZ 2007 3.1 1.9 1.2 1.2 3.3 4.2 1.4 4.0 9.0 11.3
XYZ 2008 2.5 3.9 3.5 2.1 1.3 3.9 3.9 3.4 11.2 13.3
Heh... if ya wanna practice using 2 Pivots to solve this, I'd be happy to have you post it here for others to see. But, what I'm really after is doing this with a single Pivot... I don't think it can be done. The hard part is the fact that there's two different aggragates here. I believe a Pivot can only operate on a single aggragate.
If you're really sure that it can't be done using a single Pivot, I sure don't mind you saying so... the more the merrier. 😀
Thanks for the help, folks.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2008 at 8:44 pm
Just in case someone wants to "play", here's the Cross Tab code I'm trying to duplicate with a single pivot...
SELECT Company,
Year,
STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [Q1 Amt],
STR(SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END),5,1) AS [Q1 Qty],
STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [Q2 Amt],
STR(SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END),5,1) AS [Q2 Qty],
STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [Q3 Amt],
STR(SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END),5,1) AS [Q3 Qty],
STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [Q4 Amt],
STR(SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END),5,1) AS [Q4 Qty],
STR(SUM(Amount),7,1) AS [Total Amt],
STR(SUM(Quantity),7,1) AS [Total Qty]
FROM #SomeTable
GROUP BY Company, Year
ORDER BY Company, Year
... and here's the Pivot code... but it only does one of the aggragates (Amount)... I don't know how to make it do two aggragates in a single Pivot like the Cross Tab can... and I don't think it can be done. Everything I read about Pivot says it can't be done and I'm looking for some confirmation of that fact from some of the more experienced 2k5 folks... thanks again.
SELECT
Year,
ISNULL([1],0) AS [Q1 Amt],
ISNULL([2],0) AS [Q2 Amt],
ISNULL([3],0) AS [Q3 Amt],
ISNULL([4],0) AS [Q4 Amt],
ISNULL([1],0) + ISNULL([2] ,0) + ISNULL([3],0) + ISNULL([4],0) AS Total
FROM (SELECT Year,Quarter,Amount FROM #SomeTable) AS src
PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt
ORDER BY Year
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2008 at 9:31 pm
Not exactly a single pivot - but 😎
SELECT amt.Company
,amt.Year
,COALESCE(amt.[1], 0.00) As Q1Amt
,COALESCE(qty.[1], 0.00) As Q1Qty
,COALESCE(amt.[2], 0.00) As Q2Amt
,COALESCE(qty.[2], 0.00) As Q2Qty
,COALESCE(amt.[3], 0.00) As Q3Amt
,COALESCE(qty.[3], 0.00) As Q3Qty
,COALESCE(amt.[4], 0.00) As Q4Amt
,COALESCE(qty.[4], 0.00) As Q4Qty
,COALESCE(amt.[1], 0.00) + COALESCE(amt.[2], 0.00) + COALESCE(amt.[3], 0.00) + COALESCE(amt.[4], 0.00) As TotalAmt
,COALESCE(qty.[1], 0.00) + COALESCE(qty.[2], 0.00) + COALESCE(qty.[3], 0.00) + COALESCE(qty.[4], 0.00) As TotalAmt
FROM (SELECT Company, Year, Quarter, Amount From #SomeTable) t1
PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
INNER JOIN (SELECT Company, Year, Quarter, Quantity FROM #SomeTable) t2
PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
ON qty.Company = amt.Company AND qty.Year = amt.Year
ORDER BY amt.Company, amt.Year;
But, it is done in a single query.
Edit: Forgot to include to totals.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2008 at 9:50 pm
Thanks, Jeff! Great example of how to do it with 2 derived tables... 🙂
Does anyone know how to do it using a single pivot or are we relegated to using derived tables to solve this problem? If we need to use Pivot instead of a Cross Tab, I think methods similar to Jeff W's method are going to be required... I don't believe it can be done using a single Pivot but I don't know for sure... Anyone know for sure?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2008 at 9:55 pm
I don't think it is possible - and, you have to use the derived table (or a CTE) or you get some very interesting results. Try to pivot straight from the table and you will see what I mean.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2008 at 11:18 pm
Jeff Moden (6/22/2008)
I'm trying to find out if there's a way to do it using a single Pivot.
Heh. What were we saying just yesterday about posters and their unreasonable, artificial restrictions? 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 22, 2008 at 11:20 pm
Okay, it can be done in a single pivot - but I had to cheat and performance will probably suffer. As far as I have been able to determine, it cannot be done without using a derived table or CTE. But, if we use a CTE we can get it done using a single pivot statement.
;WITH data (Company, Year, QuarterName, Value)
AS (
SELECT Company
,Year
,'Amt' + CAST(Quarter AS CHAR(1))
,Amount
FROM #SomeTable
UNION ALL
SELECT Company
,Year
,'Qty' + CAST(Quarter AS CHAR(1))
,Quantity
FROM #SomeTable
)
SELECT Company
,Year
,COALESCE(amt1, 0.00) As Q1Amt
,COALESCE(qty1, 0.00) As Q1Qty
,COALESCE(amt2, 0.00) As Q2Amt
,COALESCE(qty2, 0.00) As Q2Qty
,COALESCE(amt3, 0.00) As Q3Amt
,COALESCE(qty3, 0.00) As Q3Qty
,COALESCE(amt4, 0.00) As Q4Amt
,COALESCE(qty4, 0.00) As Q4Qty
,COALESCE(amt1, 0.00) + COALESCE(amt2, 0.00) + COALESCE(amt3, 0.00) + COALESCE(amt4, 0.00) As TotalAmt
,COALESCE(qty1, 0.00) + COALESCE(qty2, 0.00) + COALESCE(qty3, 0.00) + COALESCE(qty4, 0.00) As TotalQty
FROM data t
PIVOT (SUM(Value) FOR QuarterName IN (Amt1, Amt2, Amt3, Amt4, Qty1, Qty2, Qty3, Qty4)) AS pvt
ORDER BY Company, Year;
This really does not seem to be any easier, but again - it does work (probably not very well).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 23, 2008 at 5:18 am
Jeffrey Williams (6/22/2008)
I don't think it is possible - and, you have to use the derived table (or a CTE) or you get some very interesting results. Try to pivot straight from the table and you will see what I mean.
Yeah... I found that out earlier.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2008 at 5:22 am
rbarryyoung (6/22/2008)
Jeff Moden (6/22/2008)
I'm trying to find out if there's a way to do it using a single Pivot.Heh. What were we saying just yesterday about posters and their unreasonable, artificial restrictions? 😀
Heh.. true enough, Barry. The restrictions here are definitely artificial because, like I said in the original post, I'm trying to figure out if it can be done using a single Pivot and I don't know enough about Pivot to really say... so I'm looking for some help from the folks with a lot more experience in 2k5 than I.
At least I provided some data and showed my work, eh? I should write an article about that... wait... 😛
Looks like Jeff W. may have done it... I gotta look...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2008 at 5:42 am
Jeffrey Williams (6/22/2008)
Okay, it can be done in a single pivot - but I had to cheat and performance will probably suffer. As far as I have been able to determine, it cannot be done without using a derived table or CTE. But, if we use a CTE we can get it done using a single pivot statement.
Outstanding, there Mr. Williams! Very clever, indeed. And, yes... you are correct, performance does take quite a hit... On a million row test, instead of it taking around 5 seconds, it takes almost 21 seconds even with indexing.
Here's the million row test table I used...
DROP TABLE dbo.jbmTest
GO
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "Company" has a range of "AAA" to "BBB" non-unique 3 character strings
-- Column "Amount has a range of 0.0000 to 9999.9900 non-unique numbers
-- Column "Quantity" has a range of 1 to 50,000 non-unique numbers
-- Column "Date" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Columns Year and Quarter are the similarly named components of Date
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
Company = CHAR(ABS(CHECKSUM(NEWID()))%2+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%2+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%2+65),
Amount = CAST(ABS(CHECKSUM(NEWID()))%1000000/100.0 AS MONEY),
Quantity = ABS(CHECKSUM(NEWID()))%50000+1,
Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
Year = CAST(NULL AS SMALLINT),
Quarter = CAST(NULL AS TINYINT)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
UPDATE dbo.jbmTest
SET Year = DATEPART(yy,Date),
Quarter = DATEPART(qq,Date)
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
Thanks for the help, folks. Jeff W.... thanks for the great example of SQL prestidigitation... cheating is good! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2008 at 6:18 am
You didn't dare to post the question on "the other forum"? 😀
Here is a single-query suggestion with only one PIVOT operator.
SELECTp.Company,
p.[Year],
COALESCE(p.[Q1 Amt], 0) AS [Q1 Amt],
CAST(COALESCE(p.[Q1 Qty], 0) AS INT) AS [Q1 Qty],
COALESCE(p.[Q2 Amt], 0) AS [Q2 Amt],
CAST(COALESCE(p.[Q2 Qty], 0) AS INT) AS [Q2 Qty],
COALESCE(p.[Q3 Amt], 0) AS [Q3 Amt],
CAST(COALESCE(p.[Q3 Qty], 0) AS INT) AS [Q3 Qty],
COALESCE(p.[Q4 Amt], 0) AS [Q4 Amt],
CAST(COALESCE(p.[Q4 Qty], 0) AS INT) AS [Q4 Qty],
COALESCE(p.[Total Amt], 0) AS [Total Amt],
CAST(COALESCE(p.[Total Qty], 0) AS INT) AS [Total Qty]
FROM(
SELECTu.Company,
u.[Year],
CASE
WHEN u.Quarter IS NULL AND u.theCol = 'Amount' THEN 'Total Amt'
WHEN u.Quarter IS NULL AND u.theCol = 'Quantity' THEN 'Total Qty'
WHEN u.theCol IS NOT NULL THEN 'Q' + STR(u.Quarter, 1) + CASE WHEN u.theCol = 'Amount' THEN ' Amt' ELSE ' Qty' END
ELSE NULL
END AS theColumn,
SUM(u.theValue) AS theValue
FROM(
SELECTCompany,
[Year],
Quarter,
Amount,
CAST(Quantity AS MONEY) AS Quantity
FROMjbmtest
) AS s
UNPIVOT(
theValue
FOR theCol IN ([Amount], [Quantity])
) AS u
GROUP BYu.Company,
u.[Year],
u.theCol,
u.Quarter,
'Q' + STR(u.Quarter, 1) + CASE WHEN u.theCol = 'Amount' THEN ' Amt' ELSE ' Qty' END
WITH ROLLUP
) AS w
PIVOT(
MAX(w.theValue)
FOR w.TheColumn IN ([Q1 Amt], [Q1 Qty], [Q2 Amt], [Q2 Qty], [Q3 Amt], [Q3 Qty], [Q4 Amt], [Q4 Qty], [Total Amt], [Total Qty])
) AS p
WHEREp.[Year] IS NOT NULL
ORDER BYp.Company,
p.[Year]
N 56°04'39.16"
E 12°55'05.25"
June 23, 2008 at 6:49 am
Peso (6/23/2008)
You didn't dare to post the question on "the other forum"? 😀
Nah... I knew great minds like yours would eventually show up... Besides... I didn't want to get one of those famous "Already answered here" messages 😛
Thanks for the code, Peter.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2008 at 7:13 am
-- Peso 2
SELECTp.Company,
p.[Year],
COALESCE(p.Q1Amount, 0.0) AS [Q1 Amt],
CAST(COALESCE(p.Q1Quantity, 0) AS INT) AS [Q1 Amt],
COALESCE(p.Q2Amount, 0.0) AS [Q2 Amt],
CAST(COALESCE(p.Q2Quantity, 0) AS INT) AS [Q2 Amt],
COALESCE(p.Q3Amount, 0.0) AS [Q3 Amt],
CAST(COALESCE(p.Q3Quantity, 0) AS INT) AS [Q3 Amt],
COALESCE(p.Q4Amount, 0.0) AS [Q4 Amt],
CAST(COALESCE(p.Q4Quantity, 0) AS INT) AS [Q4 Amt],
COALESCE(p.Q1Amount, 0.0) + COALESCE(p.Q2Amount, 0.0) + COALESCE(p.Q3Amount, 0.0) + COALESCE(p.Q4Amount, 0.0) AS [Total Amt],
CAST(COALESCE(p.Q1Quantity, 0) + COALESCE(p.Q2Quantity, 0.0) + COALESCE(p.Q3Quantity, 0.0) + COALESCE(p.Q4Quantity, 0.0) AS INT) AS [Total Qty]
FROM(
SELECTu.Company,
u.[Year],
'Q' + STR(u.[Quarter], 1) + u.theCol AS QuarterName,
SUM(u.[Value]) AS [Value]
FROM(
SELECTCompany,
[Year],
[Quarter],
Amount,
CAST(Quantity AS MONEY) AS Quantity
FROMjbmTest
) AS s
UNPIVOT(
[Value]
FOR theCol IN (s.Amount, s.Quantity)
) AS u
GROUP BYu.Company,
u.[Year],
u.[Quarter],
u.theCol
) AS d
PIVOT(
MAX([Value])
FOR d.QuarterName IN ([Q1Amount], [Q1Quantity], [Q2Amount], [Q2Quantity], [Q3Amount], [Q3Quantity], [Q4Amount], [Q4Quantity])
) AS p
ORDER BYp.Company,
p.[Year]
N 56°04'39.16"
E 12°55'05.25"
June 23, 2008 at 7:44 am
Haven't checked it for accuracy, but that's more like it... getting real close to the Cross Tab version of the code for performance. Thanks, Peter.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2008 at 7:46 am
SQL Profiler
CPU Dur Reads
---- ----- -----
Jeff 2531 4270 5304
JW 2 9938 16935 10608
Peso 2 4078 6846 5304
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply