Dynamic value on Pivot

Question

Post reply

Dynamic value on Pivot

sushil_dwid

Ten Centuries

Points: 1241
More actions
September 21, 2015 at 12:01 am

#307813

Hi,
Can we pass dynamic values while pivoting? Please help if it is possible.
Here is example
Declare @a date, @b-2 date
set @a='2015-09-08 22:19:29.330'
set @b-2='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-10 00:00:00.000','2015-09-10'
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-11 00:00:00.000','2015-09-11'
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-12 00:00:00.000','2015-09-12'
/* Example table, not necessary in solution. */
DECLARE @MyTable TABLE(
Country varchar (250),
MessageType varchar(250),
id INT,
updatetimestamp date)
/* Sample data. */
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Email',1, '2015-09-09');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Phone',1, '2015-09-09');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Blackberry',1, '2015-09-09');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','SMS',1, '2015-09-10');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Phone',1, '2015-09-10');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Email',1, '2015-09-10');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Email',1, '2015-09-11');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','SMS',1, '2015-09-11');
INSERT INTO @MyTable(Country, MessageType, id, updatetimestamp)
VALUES('India','Blackberry',1, '2015-09-11');
SELECT Country,
MessageType,
ID,
updatetimestamp into #temp
from @MyTable
select * from #temp
pivot(count(id) for updatetimestamp in(select cast(Full_Date as DATE) from #DateTemp))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 1

sushil_dwid

Ten Centuries

Points: 1241

September 21, 2015 at 12:23 am

#1827987

Got the solution.. Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 2

sushil_dwid (9/21/2015)
Got the solution.. Thanks

Care to share your solution so that others can learn?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 3

I took a variable and stored all dynamic columns on that an used same on pivot query.

declare @sql nvarchar(500)

DECLARE @col NVARCHAR(300)

SELECT @col = ISNULL(@col + ', ', '') + QUOTENAME(Full_Date)

FROM #DateTemp

SET @sql='

select Country,MessageType, ' + @col + ' from #temp

pivot(count(id) for updatetimestamp in(' + @col + ')) as Pv order by 1,2'

EXEC sp_executesql @sql