Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

using join in PIVOT Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 10:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 11:31 AM
Points: 175, Visits: 351
Sure and i didn't tweak the pivot functionality as the data structure i gave is for testing purpose. but the logic will remains same. here is the full code.

use tempdb;

set nocount on;
if OBJECT_ID('dbo.TallyTable') is not null drop table dbo.TallyTable
go
select top 100 IDENTITY(int,1,1) as ID

into dbo.TallyTable from master.dbo.syscolumns

alter table dbo.TallyTable add constraint pk_tallytableId primary key clustered(ID)

GO

if OBJECT_ID('tempdb..#company') is not null
drop table #company

create table #company
(
Name varchar(20),
DaysInLate int
)

insert #company
select 'microsoft' as Name, 15 as daysinlate union all
select 'nokia' as name, 10 as daysinlate union all
select 'Google' as name, 13 as daysinlate

if OBJECT_ID('tempdb..#data') is not null
drop table #data

create table #data
(
Name varchar(20),
DataReceived datetime,
RecordsCount int
)

insert #data
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount;

declare @cols nvarchar(4000);
DECLARE @Query nvarchar(4000);

with MyDates as
(
select ID, DATEADD(DAY, ID - 1, '2013-08-01') as MyDate
from dbo.TallyTable t
where t.ID <= 31
)

select @cols= Records.Output from
(
SELECT STUFF(
(
SELECT ',[' + CONVERT(VARCHAR(10), MyDate, 101) + ']' from MyDates

for xml path('')
), 1, 1, '') as Output) Records;




set @Query = 'SELECT * from
(
select Name, MyDate, case when DataReceived IS not null then cast(RecordsCount as varchar(15)) else (select top 1 stuff(stuff(CONVERT(varchar, DATEADD(DAY, c.DaysInLate, md.MyDate), 112), 5, 0, '-'), 8, 0, '-') from #data d2 join #company c on d2.Name = c.Name where c.Name = microsoft) end as MyNewValue
from MyDates md
left join #data d on d.DataReceived = md.MyDate
)
as p PIVOT ( max([MyNewValue]) FOR [MyDate]
IN ('+ @cols+')) AS pvt';


EXEC sp_executesql @Query


.


If you have time could you please suggest me how can i make pivot using dynamic cross tabs. i ma aware of pivot but not dynamic cross tabs.

Thank you.
Post #1542215
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse