strange string condition - 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)

strange string condition

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2011-05-31 : 04:26:48

Hi,


declare @tab1 table
(
VehicleID varchar(10),
FirstOccur varchar(10)
)

insert into @tab1 values  ('AB1','1CC') 
insert into @tab1 values  ('AB1','1AA') 
insert into @tab1 values  ('AB1','1BB') 
insert into @tab1 values  ('AB2','2DD') 
insert into @tab1 values  ('AB2','2EE') 
insert into @tab1 values  ('AB3','3FF') 
insert into @tab1 values  ('AB3','3GG') 
insert into @tab1 values  ('AB3','3HH') 
insert into @tab1 values  ('AB3','3II') 
insert into @tab1 values  ('AB4','4AA')

I need to create a complicated query (atleast for me) to generate the foll. output.



--required output:

VehicleID	FirstOccur	              AddnlOccur
AB1			1CC			1AA, 1BB
AB2			2DD			2EE
AB3			3FF			3GG, 3HH
AB4			4AA

the column 'AddnlOccur' is a computed column...
as shown above, firstoccur column will contain ONLY the first occurrence corresponding to each VehicleID.
Next any occurrence will be added to the AddnlOccur column.

Any pointers will be helpful.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-31 : 08:56:12

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-01 : 19:01:29

Madhivanan, gave you a link for one way to contatenate the string for the AddnlOccur column. But, tables have no order. So, unless you have another column that you can order by you will get random FirstOccur and AddnlOccur values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources