combining two rows of data into 1

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)

combining two rows of data into 1

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-20 : 10:28:15

hi i have my data coming in like this


ClassName	NAV	SharesOutstanding
Class A GBP	23704633.79	
Class A GBP		20143018.57

but what i want to happen is is showing like this


ClassName	NAV	SharesOutstanding
Class A GBP	23704633.79 20143018.57

how can this be done

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-20 : 12:27:42

You can join the table onto itself, joining on ClassName, and on one side, pick only the NAV rows, and on the other side, only the Sharesoutstanding rows. Another alternative is to use an aggregate function - e.g. like this:

SELECT ClassName, MAX(Nav) AS Nav, MAX(SharesOutstanding) AS SharesOutstanding
FROM YourTable
GROUP BY ClassName;

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 05:10:45

Hi thanks for reply
was trying this code


UPDATE dbo.BNYWorkingNAVTable
   SET SharesOutstanding = (SELECT MAX(SharesOutstanding) AS SharesOutstanding
FROM dbo.BNYWorkingNAVTable
GROUP BY ClassName

                )

getting the following error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

any ideas around this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 05:16:09

is that the full query ?

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 05:20:33

this id the full thing im working on so far but i dont no if its even right

UPDATE dbo.BNYWorkingNAVTable
SET SharesOutstanding = (SELECT MAX(SharesOutstanding) AS SharesOutstanding
FROM dbo.BNYWorkingNAVTable
GROUP BY ClassName)
WHERE ClassName= ClassName`

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 05:44:31

the error message clearly stats that the sub-query

(SELECT MAX(SharesOutstanding) AS SharesOutstanding FROM dbo.BNYWorkingNAVTable GROUP BY ClassName)

returns more than 1 row

Are you trying to set SharesOutstanding for all rows to the MAX(SharesOutstanding) of the same table ?


UPDATE dbo.BNYWorkingNAVTable
SET   SharesOutstanding = (
                              SELECT MAX(SharesOutstanding) AS SharesOutstanding
                              FROM   dbo.BNYWorkingNAVTable
                              GROUP BY ClassName
                          )
WHERE ClassName= ClassName   -- this line does not make sense, as it will be TRUE anyway

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 05:49:37

my table is like this at min


K_ID	FundCode	ACCOUNTPERIOD	ClassName	NAV	SharesOutstanding
4	IL01	12/31/2013	Class A GBP	869758.06	
5	IL01	12/31/2013	Class A GBP		20143018.57

7	IL01	12/31/2013	Class I2 GBP	869758.06	
8	IL01	12/31/2013	Class I2 GBP		210634139.7
	
10	IL01	12/31/2013	Class I2 EUR Hedged	869758.06	
11	IL01	12/31/2013	Class I2 EUR Hedged		657920807.6

and what i want the table to look like after this is


4	IL01	12/31/2013	Class A GBP	869758.06       20143018.57	
8	IL01	12/31/2013	Class I2 GBP	869758.06	210634139.7

and i want this to happen for whole table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 05:56:53

[code]
DELETE D
FROM (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SharesOutstanding DESC)
FROM yourtable
) D
WHERE D.RN <> 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 06:11:16

that kind of works but it keeps setting the nav column to nothing i want to keep the figures in that column as well

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 08:31:45

don't quite understand. Please elaborate

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 09:02:44

it has no values in the nav column in the table

table is like this


K_ID	FundCode	ACCOUNTPERIOD	ClassName	NAV	SharesOutstanding
4	IL01	12/31/2013	Class A GBP	869758.06	
5	IL01	12/31/2013	Class A GBP		20143018.57

7	IL01	12/31/2013	Class I2 GBP	869758.06	
8	IL01	12/31/2013	Class I2 GBP		210634139.7
	
10	IL01	12/31/2013	Class I2 EUR Hedged	869758.06	
11	IL01	12/31/2013	Class I2 EUR Hedged		657920807.6

whith ur sql i get this output



K_ID	FundCode	ACCOUNTPERIOD	ClassName	NAV	SharesOutstanding
4	IL01	12/31/2013	Class A GBP				20143018.57
		  

7	IL01	12/31/2013	Class I2 GBP		210634139.7
10	IL01	12/31/2013	Class I2 EUR Hedged	657920807.6

i want the values in both columns right beside each other like this



4	IL01	12/31/2013	Class A GBP	869758.06       20143018.57	
8	IL01	12/31/2013	Class I2 GBP	869758.06	210634139.7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 09:12:22

can you edit your last post and use [ code ] tag to align the data ? or preferable, post your sample data in consumable format like


declare @sample table
(
  col1 int,
  col2 int
  . . . 
)
insert into @sample select 10, 20
insert into @sample select 11, 22

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 09:15:16

i put the code tags around it

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 09:20:34

declare @sample table
(
K_ID,
FundCode nvarchar(250),
ACCOUNTPERIOD nvarchar(250) ,
ClassName nvarchar(250),
NAV nvarchar(250),
SharesOutstanding nvarchar(250)
)
insert into @sample select IL01 ,2/31/2013,Class A GBP,1234,869758.06
insert into @sample select IL01,12/31/2013,Class A GBP ,1234, 20143018.57
insert into @sample select IL01 ,2/31/2013,Class 12 GBP,1234,869758.06
insert into @sample select IL01 ,2/31/2013,Class 12 GBP,1234,869758.06
insert into @sample select IL01 ,2/31/2013,Class 14 GBP,12345,869758.06
insert into @sample select IL01 ,2/31/2013,Class 42 GBP,12345,869758.06

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 09:24:01

[code]
SELECT FundCode, ACCOUNTPERIOD, ClassName, NAV, MIN(SharesOutstanding), MAX(SharesOutstanding)
FROM @sample
GROUP BY FundCode, ACCOUNTPERIOD, ClassName, NAV
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 09:36:54

how do i get the update to work then that's what im stuck with

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 09:41:30

update ? what column you want to update with ?

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-21 : 10:00:34

well i need it to show up like that in the table going forward not with 2 seperate rows eatch time. is the select going to do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-21 : 20:39:50

quote:
Originally posted by rjhe22

well i need it to show up like that in the table going forward not with 2 seperate rows eatch time. is the select going to do this?

then just use the SELECT query.

If you want to update back the original table, then you will need 2 columns of SharesOutstanding like min_SharesOutstanding and max_SharesOutstanding and also delete those unwanted rows

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-22 : 04:27:43

ya i want it to update back in the orignal table will be running querys of that table and need the info showing up the way i asked for.

why have a min and a max column?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-22 : 04:52:20

quote:
Originally posted by rjhe22

ya i want it to update back in the orignal table will be running querys of that table and need the info showing up the way i asked for.

why have a min and a max column?

which column do you want to update to ?

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources