Data should be merged according to same criteria i

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

General SQL Server Forums

New to SQL Server Programming

Data should be merged according to same criteria i

Author

Topic

ankita.vinculum
Starting Member

5 Posts

Posted - 2012-06-05 : 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
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-05 : 10:14:48

Your roles should be in their own table. you do NOT store data as comma separated values in a column!!!!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-05 : 12:08:02

As Don said you should normalize your data structure. However, I can't tell what data goes with which column. Can you post DDL, DML and expected output.

Here are some links that might help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 12:24:18

From what I see, I guess what you're after is something like this

http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

But again posted data is not in proper format so if its not what you want, please post data in required format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ankita.vinculum
Starting Member

5 Posts

Posted - 2012-06-07 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources