[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Pivot

Posted on 2011-03-15
15
Medium Priority
?
451 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 2000 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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