dynamic pivot troubles :/

Question

dynamic pivot troubles :/

manssourianm

Mr or Mrs. 500

Points: 556
More actions
May 29, 2014 at 10:49 am

#396834

Hi, I'm trying to make a query that does a dynamic pivot on the date field. I would like a new date column for every date that gets added in the data.
I tried to adapt the query I found in the 'Scripts' section of this website, to no avail. Any help would be appreciated!
I would like to get from this:
create table before
(
date datetime,
badge varchar(5),
cnt int
)
;
insert into before (date, badge, cnt) values ('2014-05-03', 'A', 8);
insert into before (date, badge, cnt) values ('2014-05-04', 'A', 3);
insert into before (date, badge, cnt) values ('2014-05-05', 'A', 3);
insert into before (date, badge, cnt) values ('2014-05-01', 'B', 9);
insert into before (date, badge, cnt) values ('2014-05-03', 'B', 7);
insert into before (date, badge, cnt) values ('2014-05-04', 'B', 6);
insert into before (date, badge, cnt) values ('2014-05-05', 'B', 5);
insert into before (date, badge, cnt) values ('2014-05-07', 'B', 5);
insert into before (date, badge, cnt) values ('2014-05-01', 'C', 1);
insert into before (date, badge, cnt) values ('2014-05-02', 'C', 2);
insert into before (date, badge, cnt) values ('2014-05-03', 'C', 2);
insert into before (date, badge, cnt) values ('2014-05-06', 'C', 9);
insert into before (date, badge, cnt) values ('2014-05-01', 'D', 6);
insert into before (date, badge, cnt) values ('2014-05-05', 'D', 3);
insert into before (date, badge, cnt) values ('2014-05-06', 'D', 4);
insert into before (date, badge, cnt) values ('2014-05-07', 'D', 3);
insert into before (date, badge, cnt) values ('2014-05-01', 'E', 7);
insert into before (date, badge, cnt) values ('2014-05-02', 'E', 6);
insert into before (date, badge, cnt) values ('2014-05-03', 'E', 9);
insert into before (date, badge, cnt) values ('2014-05-04', 'E', 7);
insert into before (date, badge, cnt) values ('2014-05-05', 'E', 1);
insert into before (date, badge, cnt) values ('2014-05-06', 'E', 1);
insert into before (date, badge, cnt) values ('2014-05-07', 'E', 3);
To this:
create table after
(
badge varchar(5),
[2014-05-01] varchar(10),
[2014-05-02] varchar(10),
[2014-05-03] varchar(10),
[2014-05-04] varchar(10),
[2014-05-05] varchar(10),
[2014-05-06] varchar(10),
[2014-05-07] varchar(10)
);
insert into after (badge, [2014-05-01], [2014-05-02], [2014-05-03], [2014-05-04], [2014-05-05], [2014-05-06], [2014-05-07])
values ('A', '', '', '8', '3', '3', '', '');
insert into after (badge, [2014-05-01], [2014-05-02], [2014-05-03], [2014-05-04], [2014-05-05], [2014-05-06], [2014-05-07])
values ('B', '9', '', '7', '6', '5', '', '5');
insert into after (badge, [2014-05-01], [2014-05-02], [2014-05-03], [2014-05-04], [2014-05-05], [2014-05-06], [2014-05-07])
values ('C', '1', '2', '2', '', '', '9', '');
insert into after (badge, [2014-05-01], [2014-05-02], [2014-05-03], [2014-05-04], [2014-05-05], [2014-05-06], [2014-05-07])
values ('D', '6', '', '', '', '3', '4', '3');
insert into after (badge, [2014-05-01], [2014-05-02], [2014-05-03], [2014-05-04], [2014-05-05], [2014-05-06], [2014-05-07])
values ('E', '7', '6', '9', '7', '1', '1', '3');
Any help is greatly appreciated!!

Viewing 14 posts - 1 through 13 (of 13 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This is well explained on the following article by Jeff Moden. You should read it (and maybe the first part as well) to fully understand how to do it.

http://www.sqlservercentral.com/articles/Crosstab/65048/

This is an example based on your sample data.

DECLARE @sql nvarchar(4000)

SELECT @sql = 'SELECT badge ' + CHAR(13) --Grouping columns

+ (SELECT CHAR(9) --Start of dynamic columns

+ ',ISNULL( CAST( SUM( CASE WHEN [date] = '''

+ CONVERT( char(8), [date], 112)

+ ''' THEN cnt END) AS varchar(10)), '''') AS ['

+ CONVERT( char(10), [date], 120) + ']' + CHAR(13)

FROM before

GROUP BY [date]

ORDER BY [date]

FOR XML PATH(''),TYPE).value('.','varchar(4000)')--End of dynamic columns

+ 'FROM before ' + CHAR(13) --Query "Footer"

+ 'GROUP BY badge ' + CHAR(13)

+ 'ORDER BY badge'

PRINT @sql --Print Test

EXEC sp_executeSQL @sql --Execute

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Here's a dynamic pivot for you... albeit with nulls instead of blanks, but given that you're having numbers in the columns without blanks, nulls seem more appropriate, and it's less complicated as well.

SET NOCOUNT ON;

CREATE TABLE #before (

[date] datetime,

badge varchar(5),

cnt int

);

insert into #before ([date], badge, cnt) values ('2014-05-03', 'A', 8);

insert into #before ([date], badge, cnt) values ('2014-05-04', 'A', 3);

insert into #before ([date], badge, cnt) values ('2014-05-05', 'A', 3);

insert into #before ([date], badge, cnt) values ('2014-05-01', 'B', 9);

insert into #before ([date], badge, cnt) values ('2014-05-03', 'B', 7);

insert into #before ([date], badge, cnt) values ('2014-05-04', 'B', 6);

insert into #before ([date], badge, cnt) values ('2014-05-05', 'B', 5);

insert into #before ([date], badge, cnt) values ('2014-05-07', 'B', 5);

insert into #before ([date], badge, cnt) values ('2014-05-01', 'C', 1);

insert into #before ([date], badge, cnt) values ('2014-05-02', 'C', 2);

insert into #before ([date], badge, cnt) values ('2014-05-03', 'C', 2);

insert into #before ([date], badge, cnt) values ('2014-05-06', 'C', 9);

insert into #before ([date], badge, cnt) values ('2014-05-01', 'D', 6);

insert into #before ([date], badge, cnt) values ('2014-05-05', 'D', 3);

insert into #before ([date], badge, cnt) values ('2014-05-06', 'D', 4);

insert into #before ([date], badge, cnt) values ('2014-05-07', 'D', 3);

insert into #before ([date], badge, cnt) values ('2014-05-01', 'E', 7);

insert into #before ([date], badge, cnt) values ('2014-05-02', 'E', 6);

insert into #before ([date], badge, cnt) values ('2014-05-03', 'E', 9);

insert into #before ([date], badge, cnt) values ('2014-05-04', 'E', 7);

insert into #before ([date], badge, cnt) values ('2014-05-05', 'E', 1);

insert into #before ([date], badge, cnt) values ('2014-05-06', 'E', 1);

insert into #before ([date], badge, cnt) values ('2014-05-07', 'E', 3);

DECLARE @sql AS varchar(max) = '';

DECLARE @DATES AS varchar(max) = '';

SELECT @DATES = @DATES + ', [' + CAST(CAST([date] AS date) AS varchar(10)) + ']'

FROM #before

GROUP BY CAST([date] AS date)

ORDER BY CAST([date] AS date)

SET @sql = 'SELECT badge' + @DATES + CHAR(13) + CHAR(10);

SET @sql = @sql + 'FROM #before

PIVOT (MAX(cnt) FOR [date] IN (' + RIGHT(@DATES, LEN(@DATES) - 1) + ')) AS PVT';

PRINT @sql;

EXEC (@SQL);

DROP TABLE #before;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 3

Luis, thank you very much, I was able to adapt your query to fit my needs!

The only issue now is that the 4000 limit ends up cutting off the query too early (because I have a lot of dates in there). I tried changing the data limit to (max), that didn't help, and I tried changing the datatype to other types, that didn't work either.

Any suggestions?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Did you change both values to nvarchar(max)?

One is in the variable declaration and one is in the query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Just curious if you've tried my code... Let me know...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 6

Steve,

I tried your code. I had to change it slightly, so I changed the query to:

DECLARE @sql AS varchar(max), @DATES AS varchar(max)

set @sql = ''

set @dates = ''

SELECT @DATES = @DATES + ', [' + CAST(CAST([date] AS datetime) AS varchar(10)) + ']'

FROM before

GROUP BY CAST([date] AS datetime)

ORDER BY CAST([date] AS datetime)

SET @sql = 'SELECT badge' + @DATES + CHAR(13) + CHAR(10);

SET @sql = @sql + 'FROM before

PIVOT (MAX(cnt) FOR [date] IN (' + RIGHT(@DATES, LEN(@DATES) - 1) + ')) AS PVT';

PRINT @sql;

EXEC (@SQL);

(I changed the datatype 'date' to 'datetime', and reworded the 'declare' portion of the code)

But your code didn't work, I get two errors.

one is: Msg 8114, Level 16, State 1, Line 1

Error converting data type nvarchar to datetime.

the other is: Msg 473, Level 16, State 1, Line 1

The incorrect value "May 1 201" is supplied in the PIVOT operator.

(all the dates are missing the last digit of the year).

I figure this second error is because you are taking the LEN(@dates) -1, so I removed the '-1', but that didn't help that error 🙁

I'm sorry I'm having trouble, I'm kinda thick lol

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 7

Luis,

Thanks for your help.

Changing the datatype to (max) in both places helped. Now I get the correct crosstab columns and rows, but the data part is completely blank.

I had to adapt your code slightly to fit my actual table, but I guess I did it incorrectly.

In your code where you have the line:

+ ''' THEN cnt END) AS varchar(10)), '''') AS ['

I changed it to:

+ ''' THEN 1 END) AS varchar(10)), '''') AS ['

because my actual table is not aggregated. But now the data is all blank, any suggestions?

sgmunson SSC Guru Points: 110640 More actions · Answer 8

You created the problem when you changed the CAST to datetime from date. It was cast as date because you really don't want to capture time of day as part of the fields, do you? If you do have that need, then you first need to decide on exactly what the date format will be, so that it's uniform across the columns that will be generated. You would need more than varchar(10) to handle such a construction, but the bigger question is WHY? That might lead us to a better answer.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 9

Thanks for your interest Steve. The reason I changed date to datetime is because I'm actually using SSMS 2005 (sorry I posted in the 2008 section for increased readership).

how can I adapt?

sgmunson SSC Guru Points: 110640 More actions · Answer 10

manssourianm (5/29/2014)
Steve,
I tried your code. I had to change it slightly, so I changed the query to:
DECLARE @sql AS varchar(max), @DATES AS varchar(max)
set @sql = ''
set @dates = ''
SELECT @DATES = @DATES + ', [' + CAST(CAST([date] AS datetime) AS varchar(10)) + ']'
FROM before
GROUP BY CAST([date] AS datetime)
ORDER BY CAST([date] AS datetime)
SET @sql = 'SELECT badge' + @DATES + CHAR(13) + CHAR(10);
SET @sql = @sql + 'FROM before
PIVOT (MAX(cnt) FOR [date] IN (' + RIGHT(@DATES, LEN(@DATES) - 1) + ')) AS PVT';
PRINT @sql;
EXEC (@SQL);
(I changed the datatype 'date' to 'datetime', and reworded the 'declare' portion of the code)
But your code didn't work, I get two errors.
one is: Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to datetime.
the other is: Msg 473, Level 16, State 1, Line 1
The incorrect value "May 1 201" is supplied in the PIVOT operator.
(all the dates are missing the last digit of the year).
I figure this second error is because you are taking the LEN(@dates) -1, so I removed the '-1', but that didn't help that error 🙁
I'm sorry I'm having trouble, I'm kinda thick lol

See my other reply, but if you just change varchar(10) to varchar(12), the changed code will run, but the column names in the result set will be different.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 11

manssourianm (5/29/2014)
Thanks for your interest Steve. The reason I changed date to datetime is because I'm actually using SSMS 2005 (sorry I posted in the 2008 section for increased readership).
how can I adapt?

See my other reply, but I've just now seen your reply that indicates you are on SQL 2005, so here's a re-factored query in it's entirety: