Solved

Pivot

Posted on 2011-03-15
15
440 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:parpaa
15 Comments
 
LVL 4

Expert Comment

by:LeDaouk
ID: 35138181
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35138216
There is Task in SSIS so you can easily covert it to matrix.
0
 

Author Comment

by:parpaa
ID: 35138274
Thanks LeDaouk, PatelAlpesh

I will give a try.
0
 

Author Comment

by:parpaa
ID: 35139566
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
0
 

Author Comment

by:parpaa
ID: 35139587
@patel

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

Accepted Solution

by:
Sharath earned 500 total points
ID: 35140707
I assume that you have only one record for Mobile and CallDate combination. If so, you can still use PIVOT with aggregate function. The aggregate function will be applied to only one record, so it won't be a problem.
If you know the number of Calldates, you can try providing all the dates in pivot clause. you can try like this.
select *
  from your_table
 pivot (max(Dispo) for Calldate in ([20-2-2011],[21-2-2011])) as p

Open in new window

tested on my machine.
create table #table(Mobile int, Calldate varchar(20), Dispo varchar(10))
insert #table values (234,'20-2-2011','CB'),(235,'21-2-2011','PTP')
select *
  from #table
 pivot (max(Dispo) for Calldate in ([20-2-2011],[21-2-2011])) as p
drop table #table
/*
Mobile	20-2-2011	21-2-2011
234	CB	NULL
235	NULL	PTP
*/

Open in new window

If you don't know the number of Calldates, you can go for dynamic sql. check this.
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + t2.Calldate
                           from    your_table as t2
                          order by '],[' + t2.Calldate
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select Mobile,' + @cols + 
               'from your_table
               pivot (max(Dispo) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

tested with your sample data.
create table #table(Mobile int, Calldate varchar(20), Dispo varchar(10))
insert #table values (234,'20-2-2011','CB'),(235,'21-2-2011','PTP')

declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + t2.Calldate
                           from    #table as t2
                          order by '],[' + t2.Calldate
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select Mobile,' + @cols + 
               'from #table
               pivot (max(Dispo) for Calldate in (' + @cols + ')) as p'
exec (@query)

drop table #table
/*
Mobile	20-2-2011	21-2-2011
234	CB	NULL
235	NULL	PTP
*/

Open in new window

0
 

Author Comment

by:parpaa
ID: 35144400
Thanks Sharath for the code.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:parpaa
ID: 35153979
Hi Sharath,

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

Any suggestion is highly appreciated
thnks
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 500 total points
ID: 35154047
try this.
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    your_table as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select Mobile,' + @cols + 
               'from (select Mobile,convert(varchar,CallDate,105) CallDate,Dispo from your_table) t1
               pivot (max(Dispo) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

0
 

Author Comment

by:parpaa
ID: 35154904
Thanks sharath,

Will try
0
 

Author Comment

by:parpaa
ID: 35163108
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.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35163111
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?
0
 

Author Comment

by:parpaa
ID: 35166041
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.
0
 

Author Closing Comment

by:parpaa
ID: 35179666
It worked Fantastic!!! Many thanks
0
 

Author Comment

by:parpaa
ID: 35182537
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now