Fill data from vertical column to horizontal row

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

SSIS and Import/Export (2005)

Fill data from vertical column to horizontal row

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-02-26 : 10:27:31

Hello Everyone,

I have two tables:

Table A
Date Message JobID
02/21/2009 Done 1
02/22/2009 Pending 2
02/23/2009 Done 3

Table B
JobID JobName
1 X
2 Y
3 Z

Now, I have a third table with the following columns:

Table C
Date Message X Y Z

From the above mentioned two table Table A and Table B, I want to fill the third table as below:

Table C
Date Message X Y Z
02/21/2009 Done 1
02/22/2009 Pending 2
02/23/2009 Done 3

Can anyone of you please let me know how is it possible to fill the data in this third table using SSIS.

Thank you,
notes4we

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 10:40:30

Can't you just import data into Table C from Table A?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-26 : 11:13:02

I think he means he wants data like this?

Date Message X Y Z
02/21/2009 Done 1 null null
02/22/2009 Pending null 2 null
02/23/2009 Done null null 3

notes4we, is is what u r looking for? can u clarify.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 11:41:40

Then use like this and import to Table C

Declare @T table
(Date datetime, Messages varchar(20), JobID int)

Insert @T
Select '02/21/2009','Done', 1 union all
Select '02/22/2009','Pending', 2
Select '02/23/2009', 'Done', 3 

Declare @M table
(JobID int,Jobname char(1))

Insert @M
Select 1 ,'X' union all
Select 2 ,'Y' union all
Select 3 ,'Z'

Select T.Date,T.Messages,T.JobID,J.X,J.Y,J.Z from
@T T inner join
(Select X,Y,Z
from
(Select (Case when Jobname = 'X' then JobID Else NULL END)as X,
(Case when Jobname = 'Y' then JobID Else NULL END)as Y,
(Case when Jobname = 'Z' then JobID Else NULL END)as Z
from @M)Z)J
ON J.X = t.JobID or J.Y = t.JobID or J.Z = t.JobID

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-02-26 : 14:30:24

quote:
Originally posted by vijayisonly

I think he means he wants data like this?

Date Message X Y Z
02/21/2009 Done 1 null null
02/22/2009 Pending null 2 null
02/23/2009 Done null null 3

notes4we, is is what u r looking for? can u clarify.

Yes, this is exactly the way I want to see the data.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-02-26 : 14:32:12

quote:

Originally posted by sodeep

Then use like this and import to Table C

Declare @T table
(Date datetime, Messages varchar(20), JobID int)

Insert @T
Select '02/21/2009','Done', 1 union all
Select '02/22/2009','Pending', 2
Select '02/23/2009', 'Done', 3 

Declare @M table
(JobID int,Jobname char(1))

Insert @M
Select 1 ,'X' union all
Select 2 ,'Y' union all
Select 3 ,'Z'

Select T.Date,T.Messages,T.JobID,J.X,J.Y,J.Z from
@T T inner join
(Select X,Y,Z
from
(Select (Case when Jobname = 'X' then JobID Else NULL END)as X,
(Case when Jobname = 'Y' then JobID Else NULL END)as Y,
(Case when Jobname = 'Z' then JobID Else NULL END)as Z
from @M)Z)J
ON J.X = t.JobID or J.Y = t.JobID or J.Z = t.JobID

I will try to do it this way, but the number of columns and rows is too large. Inserting one record at a time using the Insert query will be tedious. Is there any other way around?

Thank you for your reply.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-26 : 14:35:36

Huh...Sodeep has just used table declarations to illustrate his query...use your own physical tables instead of @T and @M and replace them in the SELECT query.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-02-26 : 14:56:01

Yes, I know that I have to replace everything as Sodeep has illustrated. But I was just asking if there is any other way to achieve this. In the example that I have posted, I just have 3 - 4 rows and 3 - 4 columns, but in my real case there are 1000's of rows. So, practically using the insert query will not be possible for me.

Hope you are getting what I mean.

Thanks,
notes4we

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources