November 7, 2014 at 7:46 am
Hello Everyone and Happy Friday!
I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.
But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.
This is a small example of what the rows look like in the resultset, and what I want to single to be.
DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int
, BillClerk varchar(25)
, BillCord varchar(25)
, OpsMgr varchar(25)
, VicePres varchar(25)
)
DECLARE @BillingIncorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int
, BillClerk varchar(25)
, BillCord varchar(25)
, OpsMgr varchar(25)
, VicePres varchar(25)
)
INSERT INTO @BillingIncorrect
SELECT '86555', 0, 0, 0, 0, '', '', '', '' UNION ALL
SELECT '86555', 0, 0, 0, 8040, '', '', '', 'Bugs Bunny' UNION ALL
SELECT '86555', 0, 9050, 0, 0, '', 'Daffy Duck', '', '' UNION ALL
SELECT '86555', 7030, 0, 0, 0, 'Wile E. Coyote', '', '', ''
INSERT INTO @BillingCorrect
SELECT '86555', 7030, 9050, 0, 8040, 'Wile E. Coyote', 'Daffy Duck', '', 'Bugs Bunny'
SELECT * FROM
@BillingIncorrect
SELECT * FROM
@BillingCorrect
I am not sure how to write the query to have all the data in a single row. I would greatly appreciate some assistance.
Thank you in advance for all your help, time, assistance and guidance.
Andrew SQLDBA
November 7, 2014 at 8:25 am
This should give you what you want:
select ContractNumber, MAX(pc1) as pc1, MAX(pb) as pb, MAX(om) as om, MAX(vp) as vp
, MAX(BillClerk) as BillClerk, MAX(BillCord) as BillCord, MAX(OpsMgr) as OpsMgr,
MAX(VicePres) as VicePres from @BillingIncorrect
group by ContractNumber
November 7, 2014 at 8:43 am
Daniel
That is perfect.
Thank You so very much for your help with this. I was not thinking or trying anything that simple. Greatly appreciate it.
Thanks again
Andrew SQLDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply