SQL Server Pivot-table with Numeric Columns

Question

When I run a simple query, where AskiaID is a numeric field, in MS SQL Server Management Studio, I get this :-

SELECT TaskResult, [80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443] FROM [RSM.Statistics].[dbo].[Statistic_Call] as PivotData
PIVOT
(
   COUNT(TaskResult)
   FOR RTRIM(CAST(AgentID AS CHAR)) IN ([80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443])
) AS PivotResult
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.

If I change the query to its original form :-

SELECT TaskResult, [80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443] FROM [RSM.Statistics].[dbo].[Statistic_Call] as PivotData
PIVOT
(
   COUNT(TaskResult)
   FOR AgentID IN (80444, 80421, 80438, 80435, 80046, 80427, 80378, 80442, 80419, 80436, 80379, 80410, 80439, 80437, 80405, 80377, 80383, 80406, 80420, 80443)
) AS PivotResult
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '80444'.

Any ideas what I'm doing wrong?

One thing you are doing wrong is casting to char but not specifying the length. This is very problematic...but in this case you got lucky. This post discusses why it is bad with varchar but the same logic applies to char. sqlblog.org/2009/10/09/… Of course, since you have varying lengths of data you should probably be using varchar anyway. — Sean Lange
– Sean Lange, Commented Feb 21, 2020 at 15:36

lptr · Accepted Answer · 2020-02-21 19:55:58Z

3

declare @Statistic_Call table
(
    AgentId int,
    TaskResult varchar(10)
)
insert into @Statistic_Call(AgentId, TaskResult)
values
(80444, 'Test 1'),
(80444, 'Test 2'),
(80421, 'Test 1'),
(80438, 'Test 2'),
(80435, 'Test 1'),
(80046, 'Test 1'),
(80427, 'Test 3'),
(80378, 'Test 1'),
(80442, 'Test 1'),
(80419, 'Test 4'),
(80436, 'Test 1'),
(80379, 'Test 2'),
(80410, 'Test 1'),
(80439, 'Test 1'),
(80437, 'Test 3'),
(80405, 'Test 1'),
(80377, 'Test 2'),
(80383, 'Test 1'),
(80406, 'Test 1'),
(80420, 'Test 3'),
(80443 ,'Test 1');


SELECT TaskResult, [80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443] 
FROM 
(
select TaskResult, AgentId, 1 as rownum --row_number() over (order by(select null)) as rownum
from @Statistic_Call as PivotData
) as src
PIVOT
(
   count(rownum)
   FOR AgentId IN ([80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443])
) AS PivotResult;

edited Feb 21, 2020 at 19:55

answered Feb 21, 2020 at 18:50

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Graphic Equaliser Over a year ago

<pre>

DECLARE @Columns as VARCHAR(MAX) DECLARE @SQL as VARCHAR(MAX)  SELECT @Columns=COALESCE(@Columns+',','')+QUOTENAME(AgentID) FROM (SELECT DISTINCT AgentID FROM [RSM.Statistics].[dbo].[Statistic_Agent] where TaskID=1937) AS B ORDER BY AgentID  SET @SQL='SELECT TaskResult,'+@Columns+' FROM (SELECT TaskResult,AgentID,1 AS rownum FROM [RSM.Statistics].[dbo].[Statistic_Agent] where TaskID=1937) as PivotData PIVOT (COUNT(rownum) FOR AgentID IN ('+@Columns+')) AS PivotResult' PRINT @SQL  EXEC(@SQL)

</pre>

lptr Over a year ago

nice!!! there is no actual need for a derived rownum column, you could use a non nullable column eg. TaskID ....

' FROM (SELECT TaskResult,AgentID, Taskid FROM [RSM.Statistics].[dbo].[Statistic_Agent] where TaskID=1937) as PivotData PIVOT (COUNT(Taskid) FOR AgentID....

Graphic Equaliser Over a year ago

Yes, you could avoid the use of the rownum column. I may develop this code further, for example, to perform more advanced calculations than just counting. Sums of costs or hours, say, so the rownum column is useful. Thanks for your help btw :)

Jason Goemaat · Accepted Answer · 2020-02-21 19:58:45Z

0

Have you tried using strings but not casting?

SELECT TaskResult, [80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443] FROM [RSM.Statistics].[dbo].[Statistic_Call] as PivotData
PIVOT
(
   COUNT(TaskResult)
   FOR AgentID IN ([80444], [80421], [80438], [80435], [80046], [80427], [80378], [80442], [80419], [80436], [80379], [80410], [80439], [80437], [80405], [80377], [80383], [80406], [80420], [80443])
) AS PivotResult

answered Feb 21, 2020 at 19:58

Jason Goemaat

29.3k15 gold badges89 silver badges119 bronze badges

1 Comment

lptr Over a year ago

the query will raise error: Invalid column name 'TaskResult'. because TaskResult is in the aggregation.

Collectives™ on Stack Overflow

SQL Server Pivot-table with Numeric Columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related