June 9, 2008 at 2:52 pm
Have you done anything in SSIS to copy data value from second row of one field to populate the 1st record of second field?
For example
I have 4 fields in my tables with following data:
RecordCnt EmpId School Degree
1 001 GM BS
2 001 FSU MS
3 002 FSU BS
4 003 GW BS
5 003 FSU MS
6 003 FSU PHD
...
I need to represent the data as follow:
RecordCnt EmpId School1 School2 School3 Degree1 Degree2 Degree3
1 001 GM FSU BS MS
2 002 FSU BS
3 003 GW FSU BS
MS PHD
...
I was thinking of using Pivot transformation but that's is not what I really want since
1. I'm trying to flip a column rows to many column values not column headers
2. I'm only rotating column rows to columns not try to calculate sum...
June 10, 2008 at 12:50 am
It does sound like pivot will do the trick for you though
Post a sample datafile, and we will see what we can do for you with pivot
~PD
June 10, 2008 at 6:39 am
I have a small sample records here. The last 2 records is the only one those need to be transform to the following output:
Student_ID OrganizationName SignUpDate SchoolDescription1 SchoolDescription2 PaymentPlanAmt1 PaymentPlanAmt2
544307 MDS Research Foundation, Inc. 12/7/2007
Bioorganic Synthesis Marie E. Krafft Research
145596 280288
The 1st record is the same student but at different signup date so it should not be include in the transformation.
Attached zip file contain testRecords.csv file.
The 2 fields SchoolDescription and PaymentPlanAmt are the one I need to flat out...
June 10, 2008 at 7:23 am
hey,
Sample attached
----1) create ddl (i simply inserted into ... values, there were 4 and sue me for being a bit lazy, the date I left as null, all the dates you gave didnt really help with dates)
if exists(select * from sysobjects where name = 'ExampleStudents')
begin
drop table dbo.ExampleStudents
end
create table dbo.ExampleStudents (
Student_IDInt,
[Organization Name]varchar(255),
SignUpDatedatetime,
SchoolDescriptionvarchar(255),
PaymentPlanAmountmoney)
insert into dbo.ExampleStudents values (544307,'MDS Research Foundation, Inc.',NULL,'Taxol Research',790840)
insert into dbo.ExampleStudents values (541068,'O.R. Colan Corporate, LLC',NULL,'Center for Real Estate Education & Research',25000)
insert into dbo.ExampleStudents values (544307,'MDS Research Foundation, Inc.',NULL,'Bioorganic Synthesis',145596)
insert into dbo.ExampleStudents values (544307,'MDS Research Foundation, Inc.',NULL,'Marie E. Krafft Research',280288)
-----2) Dynamic pivot - I assumed you wanted to pivot on schooldescription, and return the list of org names with those descriptions
declare @colList varchar(max)
declare @selList varchar(max)
select @colList = COALESCE(@colList + ',', '') + SchoolDescription
from (
select distinct '[' + SchoolDescription + ']' as SchoolDescription from dbo.ExampleStudents
) ColList
select @selList = COALESCE(@selList + ',', '') + SchoolDescription
from (
select distinct 'isnull([' + SchoolDescription + '], 0) as [' + SchoolDescription + ']' as SchoolDescription from dbo.ExampleStudents
) as SelList
exec (
'select piv.* from (
SELECT p.Student_ID, p.[Organization Name], ' + @sellist + ' FROM dbo.ExampleStudents
PIVOT
(
SUM(PaymentPlanAmount)
FOR SchoolDescription
IN ('+ @colList +')
) p
) piv
'
)
TADA
Hope this helps
~PD
June 10, 2008 at 7:30 am
My pivot got chopped....
declare @colList varchar(max)
declare @selList varchar(max)
select @colList = COALESCE(@colList + ',', '') + SchoolDescription
from (
select distinct '[' + SchoolDescription + ']' as SchoolDescription from dbo.ExampleStudents
--
) ColList
select @selList = COALESCE(@selList + ',', '') + SchoolDescription
from (
select distinct 'isnull([' + SchoolDescription + '], 0) as [' + SchoolDescription + ']' as SchoolDescription from dbo.ExampleStudents
--
) as SelList
exec (
'select piv.* from (
SELECT p.Student_ID, p.[Organization Name], ' + @sellist + ' FROM dbo.ExampleStudents
PIVOT
(
SUM(PaymentPlanAmount)
FOR SchoolDescription
IN ('+ @colList +')
) p
--
) piv
'
--
)
June 10, 2008 at 7:30 am
oiiii,
anywhere 😉 actually just means )
Sorry man
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply