Convert Multiple rows to 1 row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert Multiple rows to 1 row

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2011-12-19 : 09:29:06

I have data which contains trade transactions from a trading system. There are 2 scenarios found within the data.
One scenario is that 3 rows can represent a broker passing cash to an Investment vehicle. Then an Investment vehicle passing the money to a Portfolio
The other is 1 row representing broker going directly to Portfolio hence 1 line. In both cases the 3 rows or 1 row represent a trade package. However in some cases the package number is not available.
I am able to work out which rows belong to a particular package by looking at the allocation number, broker and portfolio fields.
If a row contains a valid broker and a valid portfolio then that is a package, the 1 row scenario. The valid portfolios and valid brokers are determined if they are contained in the portfolio and broker table
For the 1 row scenario a unique package number should be added to the row.
For the 3 row scenario I use the following criteria

-The allocationID within a a package are sequential.
-The first row always contains a valid broker
-The third row always contain a valid portfolio

Once packages are determined I need to add a package id number to the package. For the 3 row scenario I need to convert the 3 rows into 1 row containing the valid portfolio, valid broker plus the grossAmounts and NetAmounts
from the original first line from the package. I have provided some data to further explain the situation.



CREATE TABLE #TradeDetail  
(
                                                AllocationID int IDENTITY(1,1)
                ,       Package varchar(12)
                ,       [Broker] varchar(50)
                ,       Portfolio VARCHAR(20)
          
                ,       GrossAmount_EUR numeric(18,6)
                ,       Commission_EUR numeric(18,6)
                
                
)
     CREATE TABLE #Broker  
(
                        ID INT IDENTITY (1,1)
                ,       [Broker] varchar(50)
                           
)
     CREATE TABLE #Portfolio  
(
                        ID INT IDENTITY (1,1)
                ,       [Portfolio] varchar(50)
                           
)
                             
INSERT INTO #TradeDetail
SELECT '','Good_Broker','Investment_false','120','130' UNION ALL
SELECT '','Bad_Broker1','Investment_false','-120','-130' UNION ALL
SELECT '','Bad_Broker2','InvestCo','120','130' UNION ALL
SELECT '','Good_Broker2','Investment_false','100','150' UNION ALL
SELECT '','Bad_Broker4','Investment_false','-100','-150' UNION ALL
SELECT '','Bad_Broker5','PortfolioCO','100','150' UNION ALL
SELECT '','Good_Broker3','Investment_false','250','100' UNION ALL
SELECT '','Bad_Broker6','Investment_false','-250','-100' UNION ALL
SELECT '','Bad_Broker7','PortfolioCO2','250','100' UNION ALL
SELECT '','Good_Broker3','Investment_false','350','100' UNION ALL
SELECT '','Bad_Broker9','Investment_false','-350','-100' UNION ALL
SELECT '','Bad_Broker9','InvestCo','350','100' UNION ALL
SELECT '','Good_Broker3','Investment_false2','150','100' UNION ALL
SELECT '','Bad_Broker11','Investment_false2','-150','-100' UNION ALL
SELECT '','Bad_Broker12','InvestmentCO2','150','100' UNION ALL
SELECT '','Good_Broker3','InvestmentCO2','100','200' UNION ALL
SELECT '','Good_Broker3','InvestCo','400','300' 


INSERT INTO #Broker 
SELECT 'Good_Broker' UNION ALL
SELECT 'Good_Broker2' UNION ALL
SELECT 'Good_Broker3' 


INSERT INTO #Portfolio  
SELECT 'Investment' UNION ALL
SELECT 'InvestCo' UNION ALL
SELECT 'PortfolioCO' UNION ALL
SELECT 'PortfolioCO2' UNION ALL
SELECT 'PortfolioCO' UNION ALL
SELECT 'PortfolioCO' UNION ALL
SELECT 'InvestmentCO1' UNION ALL
SELECT 'InvestmentCO2'

With the above data the output should be as follows

AllocationID Package Broker Portfolio GrossAmount_EUR Commission_EUR
__________________________________________________________________________________________________
1 UniquepackageID Good_Broker InvestCo 120.000000 130.000000
4 UniquepackageID Good_Broker2 PortfolioCO 100.000000 150.000000
7 UniquepackageID Good_Broker3 PortfolioCO2 250.000000 100.000000
10 UniquepackageID Good_Broker3 InvestCo 350.000000 100.000000
13 UniquepackageID Good_Broker3 InvestmentCO2 150.000000 100.000000
16 UniquepackageID Good_Broker3 InvestmentCO2 100.000000 200.000000
17 UniquepackageID Good_Broker3 InvestCo 400.000000 300.000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:33:44

[code]
SELECT AllocationID ,
ROW_NUMBER() OVER (ORDER BY AllocationID) AS Package,
td.Package,td.[Broker],
prt.Portfolio,
td.GrossAmount_EUR,td.Commission_EUR
FROM #TradeDetail td
INNER JOIN #Broker b
ON B.[Broker] = td.[Broker]
CROSS APPLY (SELECT td1.Portfolio
FROM #TradeDetail td1
WHERE td1.GrossAmount_EUR = td.GrossAmount_EUR
AND td1.Commission_EUR = td.Commission_EUR
AND EXISTS(SELECT 1 FROM #Portfolio WHERE [Portfolio] = td1.[Portfolio])
)prt
ORDER BY AllocationID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sauce1979
Starting Member

47 Posts

Posted - 2011-12-19 : 16:39:06

thanks once again. i would never have thought of the cross apply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 22:26:41

welcome

see what all you can do with apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sauce1979
Starting Member

47 Posts

Posted - 2011-12-20 : 03:22:41

thanks for the link. will take a look.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 03:24:35

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources