Combing mutiple rows into one cell with conditions

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

Combing mutiple rows into one cell with conditions

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-12-17 : 19:09:28

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 00:51:36

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/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-12-19 : 01:56:25

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.

moldag
Starting Member

1 Post

Posted - 2012-12-19 : 02:54:08

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

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-12-19 : 03:11:30

I want to include the time. It needs to be precise as changes can happen multiple times a day.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 03:19:38

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/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-12-19 : 03:39:39

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 03:47:56

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/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-12-19 : 04:07:00

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 05:00:06

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/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-12-19 : 05:47:19

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 08:07:21

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources