Solved

Pivot

Posted on 2011-03-15
15
446 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need return values from a stored procedure 8 62
debug user defined function in visual studio 2015 2 47
store vs query adhoc - no show rows 4 36
SQL query 45 36
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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