June 5, 2012 at 4:31 am
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,
June 5, 2012 at 4:36 am
Welcome to SQL Server Central. Hffffff!!!!...long post right??....Do us one last favour....Go through the link in my signature....and post DDL of the tables, Sample Data and Expected Resultset as explained in the link.
June 5, 2012 at 4:53 am
Hi,
Thanks for ur rep.
Is this ur link - "How to post data/code on a forum to get the best help" - Jeff Moden
I had tried to open this link but it returns -
This webpage is not available.
The webpage at http://www.sqlservercentral.com/articles/Best+Practices/61537/ might be temporarily down or it may have moved permanently to a new web address.
June 5, 2012 at 5:14 am
ankita.vinculum (6/5/2012)
Hi,Thanks for ur rep.
Is this ur link - "How to post data/code on a forum to get the best help" - Jeff Moden
I had tried to open this link but it returns -
This webpage is not available.
The webpage at http://www.sqlservercentral.com/articles/Best+Practices/61537/ might be temporarily down or it may have moved permanently to a new web address.
I just clicked it and it opened fine.
June 5, 2012 at 5:18 am
I have tried to open ur link from google chrome as well as ie explorer but getting same issue on both - "Webpage cannot be display".
June 5, 2012 at 5:36 am
ankita.vinculum (6/5/2012)
I have tried to open ur link from google chrome as well as ie explorer but getting same issue on both - "Webpage cannot be display".
I checked it agaiun before posting this reply its still working.
Anyways, post the Create table Script of your table, post some understandable sample data which is in readily usable Format ie: Strings of Insert Statements.
And, also post the Desired Output.
This will help you in getting a faster and better solution.
June 5, 2012 at 5:57 am
This is my stored procedure which brings the data through various tables. Now I want to merge the data of those columns which have same data for these columns- WorkRole, UpdateType, User, UpdateTime & NewValue 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.
SELECT
WR.WorkRoleName 'WorkRole',
EVT.Name 'UpdateType',
US.FirstName'User',
EV.EventTime'UpdateTime',
EV.NewValue'NewValue',
FROM
Events EV
INNER JOIN Event_Type EVT ON EV.EventType = EVT.EventType
INNER JOIN Users US ON EV.UserID = US.UserID and US.IsDeleted = 0
LEFT JOIN Work_Role WR ON WR.WorkRoleCode=EV.CodeID and WR.IsDeleted = 0
WHERE
EV.AuditType = 5
ORDER BY
EV.EventTime desc
Initially my data is shown like this :
WorkRole UpdateType User UpdateTime NewValue
Admin TSL Created varsha 11-05-2012 14:10 Work Role Name=Admin TSL,
SecurityFilter=GAAdmin TSL, Description=
Admin TSL Created varsha 11-05-2012 14:10 Work Role Name=Admin TSL, Role=GAAdmin
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,
SecurityFilter=Test Test, Description=
But now I want to display the data like this :
WorkRole UpdateType User UpdateTime NewValue
Admin TSL Created varsha 11-05-2012 14:10 Work Role Name=Admin TSL, SecurityFilter=GA
Admin TSL, Description= , Role=GA Admin Role
Test Test Created Sharepoint 20-03-2012 17:22 Work Role Name=Test Test, SecurityFilter=Test
Test, Description= ,Role=Bo,
Contract Owner UAT, Default Role
I had also attach the data.xlsx which can help u in understanding my scenario.
June 5, 2012 at 10:28 pm
I have Office2003. It says File Format Not Recognized when I try to open your attachment.
I'll make this as simple as I can.
You are looking at some tables and some data which we cannot see. For us to see those tables, we would need the Scripts of those tables so that I can create the tables on my box, I would also need some Sample Data(not the way you have already posted it, its not clear which data belongs to which field) so that I can populate the tables and work a solution out for you.
If you add attachments to your post then it is not for sure that people would be willing to download these attachments. Just Generate the create Table Scripts and post them inside the "code="sql"][/code" quotes available on the left side of the posting area(when you are typing the post). That would do all the required Formatting and present everything in a understandable manner.
Edit: You are showing us the data that your Query is fetching. I am asking you to post the Structure of the table and the Sample Data of the tables on which the Query is Executing.
June 6, 2012 at 11:37 pm
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'
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply