asked on

Pivot

Hi there,

I have table like the below and want to see in Matrix table. I know this could be achieved thro Pivot, not sure how to query please help.
doesnot matter if we can do it in SSMS or in SSIS.

Mobile Calldate Dispo
234 20-2-2011 CB
235 21-2-2011 PTP
..
...
....

Desired table
Mobile 20-2-2011 21-02-2011 22-2-2011 ...
234 CB null null
235 null ptp null

LeDaouk

http://msdn.microsoft.com/en-us/library/ms177410.aspx
best example: http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Alpesh Patel

There is Task in SSIS so you can easily covert it to matrix.

parpaa

ASKER

Thanks LeDaouk, PatelAlpesh

I will give a try.

parpaa

ASKER

I guess the PIVOT can be performed when we have aggregate function in the query.

I dont have such agg functions in my requirement.
hence pivot is of no use... i guess

parpaa

ASKER

@patel

kindly please tel me more about the task used in SSIS ..

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

parpaa

ASKER

Thanks Sharath for the code.

parpaa

ASKER

Hi Sharath,

Conversion failed while converting date and or time from string data type,

Any suggestion is highly appreciated
thnks

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

parpaa

ASKER

Thanks sharath,

Will try

parpaa

ASKER

Hi Sharath,

I have the datatypes of my table as below

[Mobile No] Float
[Call date] datetime
[dispo] varchar
I am sorry should have told this before..

Apologies.

Sharath S

I got the data type of Calldate as datetime when you got the error in the last post only. Did you try my last query?

parpaa

ASKER

Sharath,

Sorry could nt get chance as our sys id was locked out. It could be done only on monday. I am sure it would work.
Thnk you so much for your help.

parpaa

ASKER

It worked Fantastic!!! Many thanks

parpaa

ASKER

Sharath,
Thanks a lot for your help, Quick Question.. What if I want the above result set into new table?
Something like Select * into [newtble] from [oldtable].. how would i do it with above resultset?

Thanks again