Concatentate Column For Duplicate Key record

Question

Concatentate Column For Duplicate Key record

Jack Corbett

SSC Guru

Points: 184394
More actions
August 8, 2008 at 3:23 pm

#192164

Okay guys here's my issue. I am ETL'ing from foxpro free tables to SQL Server and I have a table that has PersonSkills and I have duplicate records because of a comment column in FoxPro. What I'd like to do in SSIS is Get the data from FOxPro ordered by PersonNo and SKill, loop through it and if the Key fields match update the previous record setting comment = comment + next_comment. Then only continue with the record that has all the concatenated comment.
Here's the table structure in FoxPro and some data:
[font="Courier New"]CREATE TABLE PersonSkills
   (
   PersonNo CHAR(6),
   Skill VARCHAR(30),
   Comment VARCHAR(MAX) -- would be memo in FoxPro, none more than 470 characters
   )
INSERT INTO PersonSkills
   SELECT
       '012345',
       'Carpenter',
       'Makes Cabinets'
   UNION ALL
   SELECT
       '012345',
       'Carpenter',
       'Loves to Drywall'
   UNION ALL
   SELECT
       '023456',
       'Programmer',
       'Expert in VB.NET'
   UNION ALL
   SELECT
       '034567',
       'Teacher',
       'Taught 5th Grade 2 years'
   UNION ALL
   SELECT
       '034567',
       'Teacher',
       'Taught 2nd Grade 4 years'
   UNION ALL
   SELECT
       '034567',
       'Teacher',
       'Taught 9th grade math 1 year'
-- Desired results to be passed to SQL Server
PersonNo   Skill                   Comments
'012345'   'Carpenter'             'Makes Cabinets Loves to Dywall'
'023456'   'Programmer'            'Expert in VB.NET'
'034567'   'Teacher'               'Taught 5th Grade 2 years Taught 2nd Grade 4 years Taught 9th grade math 1 year'
--SQL Server Table
-- I have lookups that would convert PersonNo to Person_ID and Skill to Skill_ID
CREATE TABLE Person_Skills
   (
   Person_ID INT,
   Skill_ID INT
   Notes NVARCHAR(500)
   )
CREATE UNIQUE INDEX UX_Person_Skills_Person_Skill ON Person_Skills(Person_ID, Skill_ID)
[/font]
I could do it by building a table in SQL Server that I load all the data into and then I can process it in SQL Server, but shouldn't I be able to do it in SSIS?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004437 More actions · Answer 1

Ok... fair trade... I give you a solution... you tell me what you're using to format you code for the code entries on this forum... (please :))

--=====================================================================================================================

-- Create and populate the test table.

-- This is NOT part of the solution

--=====================================================================================================================

CREATE TABLE PersonSkills

(

PersonNo CHAR(6),

Skill VARCHAR(30),

Comment VARCHAR(MAX) -- would be memo in FoxPro, none more than 470 characters

)

INSERT INTO PersonSkills

(PersonNo, Skill, Comment)

SELECT '012345', 'Carpenter', 'Makes Cabinets' UNION ALL

SELECT '012345', 'Carpenter', 'Loves to Drywall' UNION ALL

SELECT '023456', 'Programmer', 'Expert in VB.NET' UNION ALL

SELECT '034567', 'Teacher', 'Taught 5th Grade 2 years' UNION ALL

SELECT '034567', 'Teacher', 'Taught 2nd Grade 4 years' UNION ALL

SELECT '034567', 'Teacher', 'Taught 9th grade math 1 year'

-- Desired results to be passed to SQL Server

--PersonNo Skill Comments

--'012345' 'Carpenter' 'Makes Cabinets Loves to Dywall'

--'023456' 'Programmer' 'Expert in VB.NET'

--'034567' 'Teacher' 'Taught 5th Grade 2 years Taught 2nd Grade 4 years Taught 9th grade math 1 year'

GO

CREATE FUNCTION dbo.AggregatedSkillNotes

--=====================================================================================================================

-- Function to do the required concatenation

--=====================================================================================================================

(@PersonNo INT,@Skill NVARCHAR(4000))

RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @Return NVARCHAR(4000)

SELECT @Return = COALESCE(@Return+' ','')+Comment

FROM dbo.PersonSkills

WHERE PersonNo = @PersonNo

AND Skill = @Skill

RETURN @Return

END

GO

--===== Demo the solution

SELECT PersonNo,Skill,dbo.AggregatedSkillNotes(PersonNo,Skill)

FROM dbo.PersonSkills

GROUP BY PersonNo,Skill

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

That's a fair trade. I thought you knew about this already because I learned about it from a thread that I think you were in along with Matt Miller. It is actually a hidden extra on this site found here: http://extras.sqlservercentral.com/prettifier/prettifier.aspx You just need to pick format with IFCodes.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004437 More actions · Answer 3

Dang... Yeaup... I already knew about the prettifier... was hoping there was something a bit more auto-magic... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)