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
SQL Joins: Future of the SQL Ansi Standard (Where VS Join)
How to Update with Inner Join in Oracle
How to Sum Multiple Lines in SQL
Date Column Arithmetic in Postgresql Query
Phpmyadmin - Total Record Count Varies
Executing Ssis Package with SQL Authentication
Escaping Strings Containing Single Quotes in Powershell Ready for SQL Query
Microsoft Access Query Should Return True or True and False, Only Returns True
SQL Query That Distinguishes Between ß and Ss
How to Use a Pg Sequence on a Per Record Label
How to Create a Calculated Column in a SQL Server 2008 Table
Hibernate Create Criteria to Join the Same Table Twice - Tried 2 Approach with 2 Difference Error
Slick 3.0 - Update Columns in a Table and Return Whole Table Object
Find Out the Nth-Highest Salary from Table
How to Find 11Th Entry in SQL Access Database Table