Easy way to 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 2005 Forums

Transact-SQL (2005)

Easy way to merge rows

Author

Topic

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-06-24 : 08:35:45

Hi,

I have a datetable with these data

ITEM VARCHAR(10), Qty INT
CAR 10
HOUSE 20
COMPUTER 2
....

I want to know if there is an algoritm or function better than a simple loop for merging rows in other table, i.e., 10 rows CAR, 20 rows HOUSE, 2 rows COMPUTER and so on...

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-24 : 08:57:05

What do you mean ? Can you provide some sample record and the expected result ?

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

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2007-06-24 : 09:43:39

The original table is
CAR 4
HOUSE 3
...

The new table will be
CAR
CAR
CAR
CAR
HOUSE
HOUSE
HOUSE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-24 : 09:52:17

[code]DECLARE @TABLE TABLE
(
col varchar(10),
num int
)
INSERT INTO @TABLE
SELECT 'CAR', 4 UNION ALL
SELECT 'HOUSE', 3

SELECT t.col
FROM @TABLE t
CROSS apply
-- F_TABLE_NUMBER_RANGE IS FROM http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE (1, t.num) n
/*
col
----------
HOUSE
HOUSE
HOUSE
CAR
CAR
CAR
CAR
*/[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources