Merge rows - SQL Server Forums

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)

Merge rows

Author

Topic

Sabrina W.
Starting Member

3 Posts

Posted - 2012-09-11 : 04:11:43

I'm trying to merge rows in a select according to the lfdlbnr in the first column

lfdlbnr from to
26088131 2 2 10530 194 KON 1
26088131 1 2 10530 1 KON 1

The table showes movements between storages. I want to create a view or just include it in the select statement.

at the it it should somehow look like this:
26088131 2 2 10530 1 194 KON 1

does anyone know how to do this?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-11 : 08:24:09

making some assumptions on how you want certain values. If this is not right, please provide more data, or more explanation.

create table #yak
	(lfdlbnr bigint, Y1 int, Y2 int, y3 int, y4 int, Yak varchar(5), y5 int)

insert into #yak 
	
 values 
	(26088131, 2, 2, 10530, 194, 'KON', 1)
   ,(26088131, 1, 2, 10530, 1, 'KON', 1)

select * from #yak

select 
	lfdlbnr, MAX(y1), MAX(y2), MAX(y3), MIN(y4), MAX(y4), Yak, MAX(y5)
from 
	#yak
group by
	lfdlbnr, Yak 

drop table #yak

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Sabrina W.
Starting Member

3 Posts

Posted - 2012-09-11 : 10:16:02

select lfdlbnr, lfdartnr, lfdlagnrvon,lfdlagnrnach,login,menge ,buchnr,buchtext,buchdatum from lagerbewegung
where lfdlagnrvon ='%'
or lfdlagnrnach ='%'

this is the code I do have right now....
I want to be able to see what has been moved from or to a selected storageroom...

If something is moved there are two rows written in the db - on for the outgoing storage and one for the ingoing.

I want to be able to show this in just one row.... means for every movement I want to show one instead of two rows....
the lfdlbnr Number is unique for each transaction...

I should probably mention that there are several tousand rows in this db and that we are using oracle sql.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-12 : 08:09:42

aha. Oracle is a different flavor from T-SQL. You could try asking here: http://www.dbforums.com/oracle/

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources