Vertical data into Horizontal table (cross tab)

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)

Vertical data into Horizontal table (cross tab)

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-03-05 : 10:49:37

Hello Everyone,

I have two tables:

Table A

JobID Time(in minutes) Record
1 0:15 2.1
1 0:30 0.1
1 0:45 5.4
2 0:15 2.5
2 0:30 0.7
2 0:45 4.6

I want to achieve the following.

Table B

JobID 0:15 0:30 0:45
1 2.1 0.1 5.4
2 2.5 0.7 4.6

Can anyone of you please let me know how can I cross tab the vertical table into horizontal using SSIS.

Thank you.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-05 : 11:21:05

Use PIVOT Transformation in SSIS.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-03-05 : 11:28:26

Thank you Sodeep. You atleast have given me a path which can help me to achive what I wish. I have worked with few SSIS transformations, but never used a Pivot before. I shall now do a research on it and try to achieve what I wish.

Thank you once again.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-03-05 : 11:53:31

I have a datetime column which is giving me issues and even in the pivot transformation it is stating that the columns are not mapped. I have done all what I could, but can anyone of you please let me know where can we actually MAP the columns in Pivot Transformation?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 13:16:45

This is how PIVOT is done for your example above.

SELECT jobid, [0:15], [0:30] ,[0:45]
FROM
(SELECT jobid, timemin, record
FROM @t ) ps
PIVOT
(
MAX(record)
FOR timemin IN
( [0:15], [0:30], [0:45])
) AS pvt

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 13:17:36

Data and expected output below

declare @t table (jobid int,timemin char(6),record char(5))
insert @t
select 1,'0:15','2.1' union all
select 1,'0:30','0.1' union all
select 1,'0:45','5.4' union all
select 2,'0:15','2.5' union all
select 2,'0:30','0.7' union all
select 2,'0:45','4.6'

SELECT jobid, [0:15], [0:30] ,[0:45]
FROM
(SELECT jobid, timemin, record
FROM @t ) ps
PIVOT
(
MAX(record)
FOR timemin IN
( [0:15], [0:30], [0:45])
) AS pvt

Result
-------

1 2.1 0.1 5.4
2 2.5 0.7 4.6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:23:17

http://www.databasejournal.com/features/mssql/article.php/3646531/SQL-Server-2005-Integration-Services---Part-38---Pivot-Transformation.htm

http://bisqlserver.blogspot.com/2008/08/pivot-transfomation.html

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-03-05 : 15:22:36

Thanks for all your replies.

I am now getting this error:

[Pivot [43]] Error: The pivot key value "23:45:00" is not valid.

The reason for this error is that the pivot key value is a datetime field.
Does anyone know a way around this so that I can solve the error.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:55:08

quote:
Originally posted by notes4we

Thanks for all your replies.

I am now getting this error:

[Pivot [43]] Error: The pivot key value "23:45:00" is not valid.

The reason for this error is that the pivot key value is a datetime field.
Does anyone know a way around this so that I can solve the error.

Thanks.

add a derived column task to add a new column which gives integer value corresponding to date and then pivot on it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources