How to Return Dynamic Columns from Grouped Values in SQL Server (Stored Procedure)

How to return dynamic columns from grouped values in SQL Server (Stored Procedure)

I could do it by using PIVOT command:

Case 1

select p.[Date], ISNULL([1],0)[Activity1], ISNULL([2],0)[Activity2], ISNULL([3],0)[Activity3], ISNULL([4],0)[Activity4], ISNULL([5],0)[Activity5], ISNULL([6],0)[Activity6] 
FROM
(
SELECT CONVERT(date, [Date]) [Date], ActivityId, COUNT(*) Total
from Activity
group by convert(date, [Date]), ActivityId

) AS Tb

PIVOT
(
SUM(Total)
FOR ActivityId IN ([1],[2],[3],[4],[5],[6])
) p
order by convert(date, p.[Date])

SELECT dynamic columns GROUP BY dynamic columns

Assuming @value3 is a string and is another parameter to the stored procedure, that datecolumn is in fact date, and ignoring the fact that I have no idea how you can have a schema where the grouping fields can be random like this (which you ignored in other recent questions here):

DECLARE @sql nvarchar(max) = N'SELECT ' 
+ QUOTENAME(@column1) + ', '
+ QUOTENAME(@column2) + ', SUM(amountcolumn)
FROM dbo.tablename
WHERE column3 = @value3
AND datecolumn BETWEEN @startdate AND @enddate
GROUP BY ' + QUOTENAME(@column1)
+ ', ' + QUOTENAME(@column2) + ';';

EXEC sys.sp_executesql @sql,
N'@value3 varchar(255), @startdate date, @enddate date',
@value3, @startdate, @enddate;
-- strongly recommend against sp_sqlexec
-- it is undocumented and unsupported

This also assumes you don't care about order (you probably do and will want to add ORDER BY as well as GROUP BY).

For more info on dynamic SQL and even further ways to protect yourself from user input:

  • sqlblog.org/dynamic-sql

Dynamically group a column into range

You mean like this ? without typing case statement one by one ?

select  Col,
Group_Col = convert(varchar(10), ((Col - 1) / 50) * 50 + 1)
+ ' - '
+ convert(varchar(10), ((Col - 1) / 50 + 1) * 50)
from ##TBL
order by Col

edit : adjusted the range calculation. Thanks Juan

T-SQL grouping with dynamic column number

You are looking for a PIVOT. You can use a Dynamic SQL Pivot for this. This tactic will get the column names to transform on execution of the query:

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(task)
from t1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT entity, ' + @cols + ' from
(
select entity, task, timespan
from t1
) x
pivot
(
min(timespan)
for task in (' + @cols + ')
) p '

execute(@query)

See SQL Fiddle with Demo

SQL procedure group by parameter value

You need to be very careful with this type of code as it is prone to SQL injection.

Always use QUOTENAME on identifiers. And if the column name is coming from user code then verify it.

Because @DUPLICATE_COUNTS is not in scope in the dynamic part, you need to re-declare it as an OUTPUT parameter.

CREATE PROCEDURE getEmpStats (@COLUMN_NAME varchar(20))
AS

DECLARE @DUPLICATE_COUNTS int;

DECLARE @sql nvarchar(max) = N'

SELECT @DUPLICATE_COUNTS = SUM(DUP_COUNTS)
FROM
(SELECT COUNT(*) AS DUP_COUNTS
FROM emp
GROUP BY ' + QUOTENAME(@COLUMN_NAME) + N') AS A;
';

EXEC sp_executesql @sql, N'@DUPLICATE_COUNTS int OUTPUT', @DUPLICATE_COUNTS = @DUPLICATE_COUNTS

GO

To verify the column name, use this code:

IF (NOT EXISTS (SELECT 1
FROM sys.columns c
WHERE c.name = @COLUMN_NAME AND c.object_id = OBJECT_ID(N'emp')
))
THROW 50000, 'Non existent column', 0;


Related Topics



Leave a reply



Submit