SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combing mutiple rows into one cell with conditions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/17/2012 :  19:09:28  Show Profile  Reply with Quote
I have a view that contains version data for skills, including

Name of Agent
Name of Skill
Level of Skill
Version Number of Skill
Date/Time skill level changed

For example

Agent 1, Test Skill, Level 1, v1, 10/12/12 00:00
Agent 1, Test Skill, Level 2, v2, 11/12/12 01:00
Agent 1, Test Skill 1, Level 1, v1, 11/12/12 01:00

I need to be able to combine all the skills and levels at each version date into 1 cell as follows

Agent 1, 10/12/12 00:00, Test Skill Level 1
Agent 1, 11/12/12 01:00, Test Skill Level 2 & Test Skill Level 1

I have used the following code in the past to combine all the skills and levels:

SELECT Agent,SkillandLevel,addeddate,STUFF
((SELECT ',' + Skillandlevel
FROM ViewName
WHERE Agent = h.Agent ANDFOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent,SkillandLevel,addeddate
FROM ViewName)h

As it stands this would combine all the column SkillandLevel for each agent into one cell.

How do I modify this to only combine SkillandLevel per agent for active skills - eg added date is greater than addeddate of v-1 but less than addeddate of v+1?

Any help would be appreciated

Edit: I forgot to say that I can also get the addeddate for v+1 and v-1 on the same line as the current version if required through joining duplicate tables

Edited by - sprotson on 12/17/2012 20:10:52

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  00:51:36  Show Profile  Reply with Quote
As per your posted sample data and output, this is what you want


SELECT Agent,addeddate,STUFF
((SELECT ',' + Skillandlevel
FROM ViewName
WHERE Agent = h.Agent AND addeddate = h.addeddate FOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent,addeddate
FROM ViewName)h


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

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/19/2012 :  01:56:25  Show Profile  Reply with Quote
Thanks, however I don't believe it is that simple.

That would work on the sample data, but possibly not on all data.

If the last entry in the sample data occurred even 1 second later then surely the data would not combine as the changes occurred at different times.
Go to Top of Page

moldag
Starting Member

Norway
1 Posts

Posted - 12/19/2012 :  02:54:08  Show Profile  Reply with Quote
If you are using SQL Server 2008 or later, you can cast the field addeddate as date to get rid of the time part.

SELECT Agent,addeddate,STUFF
((SELECT ',' + Skillandlevel
FROM ViewName
WHERE Agent = h.Agent AND cast(addeddate as date) = h.addeddate FOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent, cast(addeddate as date) as addeddate
FROM ViewName)h

/DM

Edited by - moldag on 12/19/2012 02:56:26
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/19/2012 :  03:11:30  Show Profile  Reply with Quote
I want to include the time. It needs to be precise as changes can happen multiple times a day.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  03:19:38  Show Profile  Reply with Quote
quote:
Originally posted by sprotson

Thanks, however I don't believe it is that simple.

That would work on the sample data, but possibly not on all data.

If the last entry in the sample data occurred even 1 second later then surely the data would not combine as the changes occurred at different times.



then tell us how eaxctly is your merge condition? whats the timeslot over which you want skills to be merged

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

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/19/2012 :  03:39:39  Show Profile  Reply with Quote
The data for each skill/level is versioned, 1, 2, 3, 4 etc

For each version I can easily get the date each version was created.

I thought it may be as simple as added between addeddate v-1 and addeddate v+1, however I can see a coiuple of issues wuth that.

1) What if there is only 1 version, so not version +1 or -1
2) What if there are only 2 versions, so no version +1
3) The skills and levels can be on different versions, eg in the sample data Test Skill is on v2 but Test Skill 1 is on v1.

Essentially I need to see the max version of each skill/level combined at the point any change is made to a skill/level (eg a new version created).

So if I addd a fee more items in the sample data:

Agent 1, Test Skill, Level 1, v1, 10/12/12 00:00
Agent 1, Test Skill, Level 2, v2, 11/12/12 01:00
Agent 1, Test Skill 1, Level 1, v1, 11/12/12 01:00
Agent 1, Test Skill 2, Level 2, v1, 12/12/02:00
Agent 1, Test Skill 3, Level 1, v1, 12/12/02:30
Agent 1, Test Skill 3, Level 2, v2, 12/12/03:00

I need to be able to combine all the skills and levels at each version date into 1 cell as follows

Agent 1, 10/12/12 00:00, Test Skill Level 1
Agent 1, 11/12/12 01:00, Test Skill Level 2 & Test Skill 1 Level 1
Agent 1, 12/12/12 02:00, Test Skill Level 2 & Test Skill 1 Leve
l 1 & Test Skill 2 Level 2
Agent 1, 12/12/12 02:30, Test Skill Level 2 & Test Skill 1 Leve
l 1 & Test Skill 2 Level 2, Test Skill 3 Level 1
Agent 1, 12/12/12 03:00, Test Skill Level 2 & Test Skill 1 Leve
l 1 & Test Skill 2 Level 2, Test Skill 3 Level 2

Does that give you enough information on what O am trying to do?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  03:47:56  Show Profile  Reply with Quote
nope..not quite...didnt understand how test skill level 2 got merged with agent 1 for 12/12/12 02:00 for example as its version is v1 and skills version is v2

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

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/19/2012 :  04:07:00  Show Profile  Reply with Quote
These are seperate skills.

At 12/12/12 02:00 Test Skill 2 Level 2 was added to Agent 1. At that time the agent already had Test Skill Level 2 and Test Skill 1 Level 1.

I need to show the mac skill/level each time any of teh skills assigned to the agent changes. An agent may have 100 skills, and each time 1 of those skills changes I need to combine all the skills/levels they have even the other 99 that did not change
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  05:00:06  Show Profile  Reply with Quote
ok..now its somewhat clear

try this and see if this is what you're after


SELECT h.Agent,h.addeddate,h.Skill + ' ' + h.Level + '&' + STUFF
((SELECT '&' + v.Skill + ' ' + v.level
FROM ViewName v
INNER JOIN (SELECT Agent,Skill,MAX(addedDate) AS latestdate
            FROM ViewName
            GROUP BY Agent,Skill
            )v1
ON v1.Agent = v.Agent
AND v1.Skill = v.Skill
AND v1.latestdate = v.addedDate
WHERE v1.Agent = h.Agent AND v1.addeddate < h.addeddate FOR XML PATH('')), 1, 1, '') AS SkillsCombined
FROM (SELECT DISTINCT Agent,addeddate,Skill,Level
FROM ViewName)h


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

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/19/2012 :  05:47:19  Show Profile  Reply with Quote
Thanks for that, not tested it yet but will do when I get a chance.

A colleague of mine just got it to work in his own way, so will be interesting to see if it is any different to your method.

Thanks Again
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  08:07:21  Show Profile  Reply with Quote
ok...let me know once you've tested...also curious on other approach as well if you could post!

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000