Solved

Pivot

Posted on 2011-03-15
15
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 41

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
 

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 41

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 41

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 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