| Author |
 Topic  |
|
|
ankita.vinculum
Starting Member
5 Posts |
 Posted - 06/05/2012 : 07:04:44
|
Hi,
Please help me out in resolving my query. I have a table which have 5 columns : WorkRole, Update Type, User, Update Time, Newvalue as mentioned below. I want to merge the data of NewValue column. Initially data display like this.
Work Role Update Type User UpdateTime NewValue
Admin TSL Created varsha 11-05-2012 14:10
Work Role Name=Admin TSL, Security Filter=GA Admin TSL, Description=
Admin TSL Created varsha 11-05-2012 14:10
Work Role Name=Admin TSL, Role=GA Admin Role
Test Test Created Sharepoint 20-03-2012 17:22 Work Role Name=Test Test, Role=Bo
Test Test Created Sharepoint 20-03-2012 17:22 Work Role Name=Test Test, Role=Contract Owner UAT
Test Test Created Sharepoint 20-03-2012 17:22 Work Role Name=Test Test, Role=Default Role
Test Test Created Sharepoint 20-03-2012 17:22 Work Role Name=Test Test, Security Filter=Test Test, Description=
I want to display data like this mention below:
Work Role Update Type User UpdateTime NewValue
Admin TSL Created varsha
11-05-2012 14:10 Work Role Name=Admin TSL, Security Filter=GA Admin TSL,
Description= , Role=GA Admin Role
Test Test Created
Sharepoint 20-03-2012 17:22 Work Role Name=Test Test, Security Filter=Test Test, Description= ,Role=Bo, Contract Owner UAT, Default Role
This means that I want to merge the data of those columns which have same data for these columns- work role, update type, user and update time and if any Work Role have more than one role for same Update Type, User and Update Time as mention in 2nd data then all the Role should come as comma separated.
I am using sql server 2008.
Thanks in advance,
ankita |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3827 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
|
|
ankita.vinculum
Starting Member
5 Posts |
Posted - 06/07/2012 : 01:45:10
|
Initially data is displaying like this
create table events (WorkRole varchar(100),UpdateType varchar(100) , Users varchar(100),UpdateTime datetime(20) , NewValue nvarchar(max))
insert into events
select 'Admin TSL', 'Created', 'varsha', '11-05-2012 14:10', 'Work Role Name=Admin TSL, Security Filter=GA Admin TSL, Description='
union
select 'Admin TSL', 'Created', 'varsha', '11-05-2012 14:10', 'Work Role Name=Admin TSL, Role=GA Admin Role'
union
select 'Test Test', 'Created', 'Sharepoint', '20-03-2012 17:22', 'Work Role Name=Test Test, Role=Bo'
union
select 'Test Test', 'Created', 'Sharepoint', '20-03-2012 17:22', 'Work Role Name=Test Test, Role=Contract Owner UAT'
union
select 'Test Test', 'Created', 'Sharepoint', '20-03-2012 17:22', 'Work Role Name=Test Test, Role=Default Role'
union
select 'Test Test', 'Created', 'Sharepoint', '20-03-2012 17:22', 'Work Role Name=Test Test, Security Filter=Test Test, Description='
Now i want to merge and then display the data if column WorkRole, UpdateType, Users, UpdateTime is same
select 'Admin TSL', 'Created', 'varsha', '11-05-2012 14:10', 'Work Role Name=Admin TSL, Security Filter=GA Admin TSL, Description= ,Role=GA Admin Role'
union
select 'Test Test', 'Created', 'Sharepoint', '20-03-2012 17:22', 'Work Role Name=Test Test, Security Filter=Test Test, Description= ,Role=Bo, Contract Owner UAT, Default Role'
ankita |
 |
|
| |
Topic |
|
|
|
Data should be merged according to same criteria i
