Is it possible to have a subquery in a PIVOT statement? Reason I'm asking is the subquery returns the desired results but doesn't work in the PIVOT:
SELECT DISTINCT Skill
FROM [a2wh].[dbo].[vw_CallLogCommon_2021]
WHERE date = '2021-10-10'
-------------------------------------------------------------------------
SELECT * FROM
(
SELECT count(call_id) as counts, call_id, date from vw_CallLogCommon_2021
group by date, call_id
) t
PIVOT(
COUNT(call_id)
FOR Skill IN (SELECT DISTINCT Skill
FROM [a2wh].[dbo].[vw_CallLogCommon_2021]
WHERE date = '2021-10-10')
AS p
October 26, 2021 at 3:07 pm
I thought about using dynamic SQL like below but there appears to be a syntax error in my dynamic part:
declare
@ColumnNames nvarchar(max),
@sql nvarchar(max)
select @ColumnNames = isnull(@ColumnNames + ', ', '') + '[' + T.Skill + ']'
from (select distinct Skill from vw_CallLogCommon_2021 where date = '2021-10-10') as t
select @sql = 'SELECT * FROM
(
SELECT count(call_id) as counts, call_id, date from vw_CallLogCommon_2021
group by date, call_id
) t
PIVOT(
COUNT(call_id)
FOR Skill IN ('
+ @ColumnNames +
') AS p'
exec sp_executesql @sql = @sql
October 26, 2021 at 3:34 pm
Quick google of this brought me to this link:
Which basically says you cannot do it because SQL is expecting a static number of columns to be returned, but a semi-dynamic number of rows. As you need the columns to be dynamic, you would need to use some dynamic SQL (ie sp_executesql). Basically, turn your queries into a variables and combine them. Something like:
DECLARE @sql NVARCHAR(MAX);
DECLARE @cols NVARCHAR(MAX);
SELECT
@cols = ISNULL( @cols + ', '
, ''
) + N'[' + [skills].[Skill] + N']'
FROM
(
SELECTDISTINCT
[Skill]
FROM[a2wh].[dbo].[vw_CallLogCommon_2021]
WHERE[date] = '2021-10-10'
) AS [skills];
SELECT
@sql = N'SELECT * FROM
(
SELECT count(call_id) as counts, call_id, date from vw_CallLogCommon_2021
group by date, call_id
) t
PIVOT(
COUNT(call_id)
FOR Skill IN (' + @cols + N')
AS p';
PRINT @sql;
--EXEC sp_executesql @sql
I did the "PRINT @sql" first so you can verify the statement prior to running it with the EXEC line. I would not recommend the above though; it is just a solution. I wouldn't recommend it as your dynamic SQL (the SELECT DISTINCT SKILL part) could result in SQL injection. There is a chance someone puts some bad code into the table in the skills column and now your dynamic SQL closes off the pivot and drops a database (for example). Lets say that (for example) you had a skill like:
') as p; DROP DATABASE msdb; select (1'
Mind you I don't think you can drop msdb like that, but as an example. The syntax would be correct, your first query would run successfully, msdb would be dropped, and a second select of the number 1 in the column p would occur.
I would be careful with sp_executesql, but it is a nice feature.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Got it, putting debug clues in like PRINT @sql helps of course to see what the dynamic is creating:
declare
@ColumnNames nvarchar(max),
@sql nvarchar(max)
select @ColumnNames = isnull(@ColumnNames + ', ', '') + '[' + t.Skill + ']'
from (select distinct Skill from vw_CallLogCommon_2021 where date = '2021-10-10') as t
select @sql = 'SELECT * FROM
(
SELECT count(call_id) as counts, call_id, date, skill from vw_CallLogCommon_2021
group by date, call_id, skill
) t
PIVOT(
COUNT(t.call_id)
FOR t.Skill IN ('
+ @ColumnNames +
')) AS p'
-- PRINT @SQL
exec sp_executesql @sql = @sql
October 26, 2021 at 3:35 pm
I don't think you need PIVOT at all in this case, unless I'm not understanding correctly:
SELECT Skill, COUNT(call_id) AS counts
FROM [a2wh].[dbo].[vw_CallLogCommon_2021]
WHERE date = '2021-10-10'
GROUP BY Skill
ORDER BY Skill
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2021 at 3:46 pm
That works even better... thanks!
November 3, 2021 at 7:11 pm
How can I pass in the date as a variable in the dynamic part of the SQL?:
--below is close but not working:
declare
@ColumnNames nvarchar(max),
@sql nvarchar(max),
@date datetime = getdate()
select @ColumnNames = isnull(@ColumnNames + ', ', '') + '[' + t.Skill + ']'
from (select distinct Skill from vw_CallLogCommon_2021 where date = @date and skill <> '[None]') as t
select @sql = 'SELECT * FROM
(
SELECT count(call_id) as counts, call_id, date, skill from vw_CallLogCommon_2021
where date = ''+ @Date +'' group by date, call_id, skill
) t
PIVOT(
COUNT(t.call_id)
FOR t.Skill IN ('
+ @ColumnNames +
')) AS p'
--PRINT @SQL
exec sp_executesql @sql = @sql
November 3, 2021 at 8:01 pm
exec sp_executesql @sql = @sql, N'@date datetime', @date
Are you sure you need a specific datetime and not a range value such as the current day?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply