Dynamically create columns sql
You will want to use a PIVOT function for this. If you have a known number of columns, then you can hard-code the values:
select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
from
(
select c.name,
cr.description,
r.typeid
from customers c
left join rewards r
on c.id = r.customerid
left join customerrewards cr
on r.typeid = cr.typeid
) x
pivot
(
count(typeid)
for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;
See SQL Fiddle with Demo.
Now if you have an unknown number of columns, then you can use dynamic SQL to PIVOT:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(description)
from customerrewards
group by description, typeid
order by typeid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name,' + @cols + ' from
(
select c.name,
cr.description,
r.typeid
from customers c
left join rewards r
on c.id = r.customerid
left join customerrewards cr
on r.typeid = cr.typeid
) x
pivot
(
count(typeid)
for description in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle With Demo
If you need to include the Total column, then you can use ROLLUP (Static Version Demo):
select name, sum([Bronze]) Bronze, sum([Silver]) Silver,
sum([Gold]) Gold, sum([Platinum]) Platinum, sum([AnotherOne]) AnotherOne
from
(
select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
from
(
select c.name,
cr.description,
r.typeid
from customers c
left join rewards r
on c.id = r.customerid
left join customerrewards cr
on r.typeid = cr.typeid
) x
pivot
(
count(typeid)
for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p
) x
group by name with rollup
Dynamic version (Demo):
DECLARE @cols AS NVARCHAR(MAX),
@colsRollup AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(description)
from customerrewards
group by description, typeid
order by typeid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsRollup
= STUFF((SELECT ', Sum(' + QUOTENAME(description) + ') as '+ QUOTENAME(description)
from customerrewards
group by description, typeid
order by typeid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT name, '+ @colsRollup + '
FROM
(
SELECT name,' + @cols + ' from
(
select c.name,
cr.description,
r.typeid
from customers c
left join rewards r
on c.id = r.customerid
left join customerrewards cr
on r.typeid = cr.typeid
) x
pivot
(
count(typeid)
for description in (' + @cols + ')
) p
) x1
GROUP BY name with ROLLUP'
execute(@query)
How can I dynamically create columns in SQL select statement
You'll need a dynamic pivot to do this. Here's the stored procedure:
CREATE PROC [dbo].[pivotsp]
@query AS NVARCHAR(MAX), -- The query, can also be the name of a table/view.
@on_rows AS NVARCHAR(MAX), -- The columns that will be regular rows.
@on_cols AS NVARCHAR(MAX), -- The columns that are to be pivoted.
@agg_func AS NVARCHAR(257) = N'SUM', -- Aggregate function.
@agg_col AS NVARCHAR(MAX), -- Column to aggregate.
@output AS NVARCHAR(257) = N'', -- Table for results
@debug AS bit = 0 -- 1 for debugging
AS
-- Example usage:
-- exec pivotsp
-- 'select * from vsaleshistory',
-- 'market,marketid,family,familyid,Forecaster,Forecasterid,product,productid',
-- 'month',
-- 'sum',
-- 'ku',
-- '##sales'
-- Input validation
IF @query IS NULL OR @on_rows IS NULL OR @on_cols IS NULL
OR @agg_func IS NULL OR @agg_col IS NULL
BEGIN
RAISERROR('Invalid input parameters.', 16, 1);
RETURN;
END
-- Additional input validation goes here (SQL Injection attempts, etc.)
BEGIN TRY
DECLARE
@sql AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@newline AS NVARCHAR(2);
SET @newline = NCHAR(13) + NCHAR(10);
-- If input is a valid table or view
-- construct a SELECT statement against it
IF COALESCE(OBJECT_ID(@query, N'U'),
OBJECT_ID(@query, N'V')) IS NOT NULL
SET @query = N'SELECT * FROM ' + @query;
-- Make the query a derived table
SET @query = N'(' + @query + N') AS Query';
-- Handle * input in @agg_col
IF @agg_col = N'*'
SET @agg_col = N'1';
-- Construct column list
SET @sql =
N'SET @result = ' + @newline +
N' STUFF(' + @newline +
N' (SELECT N'','' + quotename( '
+ 'CAST(pivot_col AS sysname)' +
+ ') AS [text()]' + @newline +
N' FROM (SELECT DISTINCT('
+ @on_cols + N') AS pivot_col' + @newline +
N' FROM' + @query + N') AS DistinctCols' + @newline +
N' ORDER BY pivot_col' + @newline +
N' FOR XML PATH(''''))' + @newline +
N' ,1, 1, N'''');'
IF @debug = 1
PRINT @sql
EXEC sp_executesql
@stmt = @sql,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT;
IF @debug = 1
PRINT @cols
-- Create the PIVOT query
IF @output = N''
begin
SET @sql =
N'SELECT *' + @newline +
N'FROM (SELECT '
+ @on_rows
+ N', ' + @on_cols + N' AS pivot_col'
+ N', ' + @agg_col + N' AS agg_col' + @newline +
N' FROM ' + @query + N')' +
+ N' AS PivotInput' + @newline +
N' PIVOT(' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'
end
ELSE
begin
set @sql = 'IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE ' +
'name = ''' + @output + ''' AND type = N''U'') DROP TABLE tempdb.' + @output
EXEC sp_executesql @sql;
SET @sql =
N'SELECT * INTO ' + @output + @newline +
N'FROM (SELECT '
+ @on_rows
+ N', ' + @on_cols + N' AS pivot_col'
+ N', ' + @agg_col + N' AS agg_col' + @newline +
N' FROM ' + @query + N')' +
+ N' AS PivotInput' + @newline +
N' PIVOT(' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col IN(' + @cols + N')) AS PivotOutput;'
end
IF @debug = 1
PRINT @sql
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
DECLARE
@error_message AS NVARCHAR(2047),
@error_severity AS INT,
@error_state AS INT;
SET @error_message = ERROR_MESSAGE();
SET @error_severity = ERROR_SEVERITY();
SET @error_state = ERROR_STATE();
RAISERROR(@error_message, @error_severity, @error_state);
RETURN;
END CATCH
With that, it's easy to pivot on a variable number of columns:
EXEC pivotsp
'SELECT TeamID, OptionGroup, OptionID AS Options FROM OptionTeam',
'Teamid', -- Row headers
'optiongroup', -- item to aggregate
'count', -- aggregation function
'optiongroup', -- Column header
'##temp' -- output table name
SELECT * FROM ##temp
Results:
Teamid 4 5
1 2 0
2 1 1
3 0 1
add column dynamically with select query
try below code
declare @a int =21
Declare @sql varchar(max)='select *,'
while @a <32
begin
set @sql = @sql + cast(@a as varchar) + ' as D' + cast(@a as varchar) + ' ,'
set @a = @a+1
end
set @sql = substring(@sql,0,len(@sql)-1) + ' from tbl1'
exec( @sql)
SQL Server select query dynamic column output
Provided the WEEK numbers and Order numbers are consistent, it is a small matter to maintain the column sequence.
You may notice I used #forecast and #article because I did not know your actual table names.
Example
Declare @SQL varchar(max) = '
Select *
From (
Select A.ArticleID
,D.Description
,B.*
From #forecast A
Join #article D on A.ArticleID=D.ArticleID
Cross Apply (values (''Week''+left(Week,4),Amount) ) B(Item,Value)
) A
Pivot (max([Value])
For [Item] in (' + Stuff((Select ','+QuoteName('Week'+left(Week,4))
From (Select Distinct top 100 [Order],Week From #forecast Order by [Order] ) A
For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL
Returns
ArticleID Description Week51 Week52 Week1 Week2 Week3
1 Test 0 150 0 200 0
Dynamically choose column in SQL query
Try this:
DECLARE @var1 VARCHAR(20)
DECLARE @sql VARCHAR(255)
SET @var1 = 'Code'
SET @sql = 'select ' + QUOTENAME(@var1) + ' from [VoucherType] where [DeletedBy] is null and [AutoID] = 1'
EXEC sp_executesql @sql
You'll have to compose a dynamic query, and execute using sp_executesql
To add more on the 'dynamic' side of things, use stored procedures. See here for an example:
http://www.marten-online.com/database/execute-dynamic-sql-in-mssql.html
That is... if you are using Microsoft SQL SERVER
Dynamically create columns in select SQL Oracle
There is no max number for titles but by looking at the data I think I can set it to a fixed number
If you can pick a maximum number of titles then you don't need to do this dynamically.
If you apply a ranking to each title for each name, with something like:
select name,
title_name,
dense_rank() over (partition by name order by title_name) as rnk
from investigator
which puts them in alphabetic order, but you can choose a different order if you prefer; then you pivot the result of that query:
select *
from (
select name,
title_name,
dense_rank() over (partition by name order by title_name) as rnk
from investigator
)
pivot (
max(title_name)
for (rnk) in (
1 as title1, 2 as title2, 3 as title3, 4 as title4
)
)
I've gone with a maximum of four titles, but you can add as many as you think you might reasonably need.
how can I name my columns Title1, title2, title3...
I've done that using aliases in the pivot's in() clause.
With your example data that gives output:
NAME TITLE1 TITLE2 TITLE3 TITLE4
---- ------ ------ ------ ------
Abu AP AR AS
Cu TA
Gyu AP
If you aren't on 11g or higher then you can do a manual pivot, which is essentiuall what Oracle is doing behind the scenes anyway:
select name,
max(case when rnk = 1 then title_name end) as title1,
max(case when rnk = 2 then title_name end) as title2,
max(case when rnk = 3 then title_name end) as title3,
max(case when rnk = 4 then title_name end) as title4
from (
select name,
title_name,
dense_rank() over (partition by name order by title_name) as rnk
from investigator
)
group by name
which gets the same result.
db<>fiddle
How can I create a dynamic Select statement within a SQL Select statement?
Dynamic SQL is usually about
- Creating a variable that contains the exact SQL you want to run
- Then using EXEC (@SQLvariable) to run that code
For example (not for production yet!) I've added a new variable @CustomSQL
DECLARE @ValueList varchar(Max);
DECLARE @TSQL varchar(Max);
DECLARE @CustomSQL varchar(Max);
SET @TSQL = {stored proc to get base query}
SET @CustomSQL =
'SELECT COALESCE(@ValueList + '','', '''') + CAST(Val AS varchar(max))
FROM (
' + @TSQL + '
) As ValuesThisYear;'
PRINT @CustomSQL
EXEC (@CustomSQL)
Notice that adding text/strings (e.g., the @TSQL variable) have to be entered as exact strings rather than their variable names. Also note apostrophes - you need to use '' every time you wish to refer to a '.
I also removed the variable name from the SELECT @ValueList = ... because the dynamic SQL cannot actually reference the variables - it has its own scope (?cannot remember the correct word) and doesn't have access to the variables. Solutions to this include
- Using a temporary table e.g., #temp which can be referenced
- Using the OUTPUT clause
Personally, I would approach it a different way - use the T-Sql provided to put data into a temporary table. Then use the temporary table in the other statement e.g.,
DECLARE @ValueList varchar(Max);
DECLARE @TSQL varchar(Max);
SET @TSQL = {stored proc to get base query}
DECLARE @CustomSQL varchar(Max)
CREATE TABLE #temp (Val varchar(1000))
SET @CustomSQL = 'INSERT INTO #temp (Val) ' + @TSQL
EXEC (@CustomSQL)
SELECT @ValueList = COALESCE(@ValueList + ',', '') + CAST(Val AS varchar(max))
FROM #temp As ValuesThisYear;
PRINT @ValList
I almost never get my dynamic SQL correct first try. Suggestions
- Keep it as simple as possible
- Before having a version that runs (e.g., EXEC (@CustomSQL)), comment the EXEC out and PRINT it instead.
Here are some examples from previous posts I've done recently
- Query for R Machine Learning Services - Filtering Categories in Where Clause
- Bottom of Dynamic columns depend on previous dynamic columns - TSQL
Related Topics
Firstname, Lastname in Sql, Too Complex
Sql Server Freetext Match - How to Sort by Relevance
Sql Access Query- Update Row If Exists, Insert If Does Not
Is Using "Not Exists" Considered to Be Bad SQL Practise
Importing and Validating Xml File Using Ssis or Just Plain T-Sql
How to Execute SQL Statements in Command Prompt (Cmd)
Select All Projects That Have Matching Tags
How to Get Just The First Row in a Result Set After Ordering
Change Data Type Varchar to Varbinary(Max) in SQL Server
T-Sql Row Number Restart After N
Unique Date Range Fields in SQL Server 2008
How to Handle 'Optional' Where Clause Filters in Sql
Comparing Comma Separated Values from Two Columns of Two Different Tables
Why Aren't Nulls Counted in Count(Columnname)
Sql Server - Filter Field Contents to Numbers Only
Insert Large Amount of Data Efficiently with Sql
How to Determine If Null Is Contained in an Array in Postgres