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.
| Author |
Topic |
|
KabirPatel
Yak Posting Veteran
54 Posts |
 Posted - 2008-01-25 : 06:44:32
|
| Hi,I have another repetitive type question......My table is as follows:JobID GenJobID RoleNo PackageID----------------------------------------------Job001 NULL Role001 Package001Job001 NULL Role002 Package001Job001 GenJob001 NULL Package001The script is as follows:create table #t1( JobID varchar(255) ,GenJobID varchar(255) ,RoleNo varchar(255) ,PackageId varchar(255))insert into #t1select 'Job001', 'GenJob001', null, 'Package001'unionselect 'Job001', null, 'Role001', 'Package001'unionselect 'Job001', null, 'Role002', 'Package001'I would like to see the data extracted in 2 ways:a) JobID GenJobID RoleNo PackageID----------------------------------------------Job001 GenJob001 Role001 Package001Job001 GenJob001 Role002 Package001in other words, whenever a GenJobID value exists it should be merged into any other records that have the same PackageID. There might be any number of Roles that match a particular Job / Package combination.Further, whenever the GenJobID is populated the RoleNo will always be null (if that helps).I have tried the following:select JobID, max(GenJobID), max(RoleNo), PackageIDfrom #t1 group by JobID, PackageIDbut it only returns JobID GenJobID RoleNo PackageID----------------------------------------------Job001 GenJob001 Role002 Package001b) I would also like to see the data as follows:JobID GenJobID RoleNo PackageID----------------------------------------------Job001 GenJob001 Role001, Role002 Package001I gather that I would only be able to do this by using a function (is that right?).Cheers,Kabir |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
A further query....