Help - Data Maintenance/Transformation

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help - Data Maintenance/Transformation

Author

Topic

koshysan
Starting Member

1 Post

Posted - 2010-05-19 : 21:39:26

I have been pulled into a project do copy data from one table into another.

The table is pretty badly setup and the data is bad.

The first table is like this
Fields - SeqID(PK),ID,ImmCode,ImmStatus,ImmDate

so the contents look like this
1 X 111 Completed 21/1/2010

the problem is that the table i need to copy into has this as the setup
Fields - ID(PK), ImmFlag1, ImmFlag2.....
where each ImmFlag represents one ImmCOde and the flag is checked if completed for each unique ID

I am trying at present to present the data in table 1 in a similar way
as table 2,

I want to pivot the contents for each ID so that
for each ID - all the ImmCodes, ImmDates and ImmStatus are represented in one row. I basically need a dynamic pivot function
that would display

To further compound the problem - there are 25 different immm codes
and 6 different imm states, a lot of the fields have blanks and nulls.
And there is more than one distinct ID,ImmCode,ImmStatus - this is due
to bad data entry and not enough validation in the data entry process.
But I now need to fix it up!!!!

I just want to know the best way to approach this problem. I am pretty
fresh to writing procedures in T-SQL / I am not new to programming -
just need to understand the way I can manipulate the data with
the select statement. The problem I am really hitting is with multiple
values of immcode and immstatus for each distinct ID. Plus I am not really sure how to tranpose the data - I thought about using CASE but that means about 50 different case statements for each possibility.

Any help appreciated!!

Running SQL Server 2005 - we have recently migrated from 2K to 2005.
Koshy

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-20 : 12:56:37

Hi

For me, the problem statement seems to be too comeplx.

Could you please convert your requirement to simple business like employee table, fruits table etc?. Then we get the technical solution and then change into your business scenario.

Please provide temporaray table creation scripts for the two tables. Then some insert statements and then what you are trying to achieve.

[May be by that time, you yourself will discover a solution too

]

Thanks
Lijo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources