March 25, 2010 at 4:39 am
Hi there,
I am trying to get data in the format of table ##Temp1 into the format of table ##Temp2
IF OBJECT_ID(N'tempdb..#Temp1') IS NULL
BEGIN
CREATE TABLE ##Temp1 (
pkTransactionID int,
ComponentText varchar(8),
DateTimeReceived datetime
)
END
GO
IF OBJECT_ID(N'tempdb..#Temp2') IS NULL
BEGIN
CREATE TABLE ##Temp2 (
TransactionID int,
TOT int,
UniqueEnvs int,
GBX int,
IEX int,
UNK int,
DateTimeReceived datetime
)
END
GO
INSERT INTO ##Temp1 (pkTransactionID, ComponentText, DateTimeReceived)
SELECT 14922061, 'TOT3', '20100319 08:05:08.207'
UNION ALL
SELECT 14922061, 'GBX1', '20100319 08:05:08.207'
UNION ALL
SELECT 14922061, 'IEX0', '20100319 08:05:08.207'
UNION ALL
SELECT 14922061, 'UNK0', '20100319 08:05:08.207'
UNION ALL
SELECT 14922392, 'TOT446', '20100319 08:09:17.830'
UNION ALL
SELECT 14922392, 'GBX298', '20100319 08:09:17.830'
UNION ALL
SELECT 14922392, 'IEX24', '20100319 08:09:17.830'
UNION ALL
SELECT 14922392, 'UNK0', '20100319 08:09:17.830'
INSERT INTO ##Temp2 (TransactionID, TOT, UniqueEnvs, GBX, IEX, UNK, DateTimeReceived)
SELECT 14922061, 3, 1, 1, 0, 0, '20100319 08:05:08.207'
UNION ALL
SELECT 14922392, 446, 322, 298, 24, 0, '20100319 08:09:17.830'
I have managed it with the query below but I am guessing there is probably a more efficient way to do this.
WITH PrintRunSummary
AS (
SELECT pkTransactionID,
CASE
WHEN SUBSTRING(ComponentText,1,3) = 'TOT'
THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)
ELSE 0
END AS [TOT],
CASE
WHEN SUBSTRING(ComponentText,1,3) = 'GBX'
THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)
ELSE 0
END AS [GBX],
CASE
WHEN SUBSTRING(ComponentText,1,3) = 'IEX'
THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)
ELSE 0
END AS [IEX],
CASE
WHEN SUBSTRING(ComponentText,1,3) = 'UNK'
THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)
ELSE 0
END AS [UNK],
DateTimeReceived
FROM ##Temp1
)
SELECT pkTransactionID TransactionID,
SUM(TOT) TOT,
SUM(GBX) + SUM(IEX) + SUM(UNK) UniqueEnvs,
SUM(GBX) GBX,
SUM(IEX) IEX,
SUM(UNK) UNK,
MAX(DateTimeReceived) DateTimeReceived
FROM PrintRunSummary
GROUP BY pkTransactionID
As you can see, the TOT, GBX, IEX and UNK values are simply the integers following the prefixes from ##Temp1. UniqueEnvs is the sum of GBX, IEX and UNK values.
If anyone can show me a more optimal approach, I'd be really appreciative.
Regards,
Paul.
P.S. The real tables this example comes from are optimally indexed.
March 25, 2010 at 4:58 am
You can use a PIVOT or a Cross-Tab. But only testing will tell which is the better option amongst all.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2010 at 5:02 am
Thanks for your response.
Sorry for sounding like a simpleton but do you have an example of how I would use PIVOT to format the query? PIVOT and UNPIVOT are next on my list of things to learn after I get to grips with CTEs so, currently, I couldn't pivot my way out of a paper bag I'm afraid.
Regards,
Paul.
March 25, 2010 at 5:27 am
SELECT pkTransactionID, [TOT],
ISNULL( [TOT], 0 ) + ISNULL( [GBX], 0 ) + ISNULL( [IEX], 0 ) + ISNULL( [UNK], 0 ) UniqueEnvS,
[GBX], [IEX], [UNK]
FROM (
SELECT pkTransactionID, LEFT( ComponentText, 3 ) ColName,
CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue, DateTimeReceived
FROM ##Temp1
) T
PIVOT( SUM( ColValue ) FOR ColName IN ([TOT],[GBX],[IEX],[UNK]) ) P
This is the Pivot method.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2010 at 5:45 am
Kingston Dhasian (3/25/2010)
SELECT pkTransactionID, [TOT],
ISNULL( [TOT], 0 ) + ISNULL( [GBX], 0 ) + ISNULL( [IEX], 0 ) + ISNULL( [UNK], 0 ) UniqueEnvS,
[GBX], [IEX], [UNK], DateTimeReceived FROM (
SELECT pkTransactionID, LEFT( ComponentText, 3 ) ColName,
CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue, DateTimeReceived
FROM ##Temp1
) T
PIVOT( SUM( ColValue ) FOR ColName IN ([TOT],[GBX],[IEX],[UNK]) ) P
This is the Pivot method.
Nice Query,
DateTimeReceived was missing in select list
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 25, 2010 at 5:51 am
Oh, I didn't see that.
SELECT P.pkTransactionID, [TOT],
ISNULL( [TOT], 0 ) + ISNULL( [GBX], 0 ) + ISNULL( [IEX], 0 ) + ISNULL( [UNK], 0 ) UniqueEnvS,
[GBX], [IEX], [UNK], MAX(DateTimeReceived) DateTimeReceived
FROM (
SELECT pkTransactionID, LEFT( ComponentText, 3 ) ColName,
CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue
FROM #Temp1
) T
PIVOT( SUM( ColValue ) FOR ColName IN ([TOT],[GBX],[IEX],[UNK]) ) P
INNER JOIN #Temp1 T1 ON P.pkTransactionID = T1.pkTransactionID
GROUP BY P.pkTransactionID, [TOT], [GBX], [IEX], [UNK]
And the below i think would be a better method called the Cross Tabs and is similar to your initial method. This is better than PIVOT for bigger tables.
SELECT T.pkTransactionID,
SUM( CASE WHEN ColName = 'TOT' THEN ColValue ELSE 0 END ) TOT,
SUM( ColValue ) UniqueEnvS,
SUM( CASE WHEN ColName = 'GBX' THEN ColValue ELSE 0 END ) GBX,
SUM( CASE WHEN ColName = 'IEX' THEN ColValue ELSE 0 END ) IEX,
SUM( CASE WHEN ColName = 'UNK' THEN ColValue ELSE 0 END ) UNK,
MAX( DateTimeReceived ) DateTimeReceived
FROM (
SELECT pkTransactionID, LEFT( ComponentText, 3 ) ColName,
CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue,
DateTimeReceived
FROM #Temp1
) T
GROUP BY T.pkTransactionID
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2010 at 6:11 am
try this select TransactionID,
sum(TOT) as TOT,
sum(UniqueEnvs) as UniqueEnvs,
sum(GBX) as GBX,
sum(IEX) as IEX,
sum(UNK) as UNX,
DateTimeReceived
from ##temp2
group by TransactionID,
DateTimeReceived
but bad thing is i didnt use ##temp1 table
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 25, 2010 at 6:22 am
after seeing Execution plan, i should say CTE query working better the PIVOT one.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 25, 2010 at 6:38 am
Bhuvnesh (3/25/2010)
after seeing Execution plan, i should say CTE query working better the PIVOT one.
Yes. And it is also faster than the Cross Tab. But as i said it may change depending on the load. So testing with the actual data will be better.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2010 at 7:07 am
Bhuvnesh (3/25/2010)
try thisselect TransactionID,sum(TOT) as TOT,
sum(UniqueEnvs) as UniqueEnvs,
sum(GBX) as GBX,
sum(IEX) as IEX,
sum(UNK) as UNX,
DateTimeReceived
from ##temp2
group by TransactionID,
DateTimeReceived
but bad thing is i didnt use ##temp1 table
I am trying to get data in the format of table ##Temp1 into the format of table ##Temp2
see the starting post
temp2 is already formatted and he is trying to get the data from temp1 in the format of temp2.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 25, 2010 at 10:59 am
Actually, the original query is a cross-tab. It just uses a CTE instead of a subquery. The Case statements in the CTE convert the rows into columns, and then the query which references the CTE does the GROUP BY and totals. There is nothing at all wrong with that approach ... it will perform just fine. Look at the execution plans and see.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2010 at 9:09 pm
Hi there,
^__^ I hope this helps
DECLARE @tbl TABLE (TransactionID INT,Code CHAR(3),Num INT, DateTimeReceived DATETIME)
INSERT INTO @tbl
SELECT pkTransactionID
--, ComponentText
, LEFT(ComponentText,3)
, RIGHT(ComponentText,LEN(ComponentText)-3)
, DateTimeReceived
FROM ##Temp1
SELECT DISTINCT
tot.TransactionID
, tot.Num AS 'TOT'
, UniqueEnvs = gbx.Num + iex.Num + unk.Num
, gbx.Num AS 'GBX'
, iex.Num AS 'IEX'
, unk.Num AS 'UNK'
, tot.DateTimeReceived
FROM @tbl tot
INNER JOIN @tbl gbx ON (tot.TransactionID=gbx.TransactionID AND gbx.Code='GBX')
INNER JOIN @tbl iex ON (tot.TransactionID=iex.TransactionID AND iex.Code='IEX')
INNER JOIN @tbl unk ON (tot.TransactionID=unk.TransactionID AND unk.Code='UNK')
WHERE tot.Code='TOT'
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
March 26, 2010 at 3:32 am
I really appreciate all of your input guys.
Many of your queries generate identical query plans to my original at small numbers of rows. I shall see if this is the same at a much higher table size and go from there.
Many thanks to you all, I've learned quite a bit from this post. 🙂
Paul.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply