?
Solved

Pivot

Posted on 2011-03-15
15
Medium Priority
?
456 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

864 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