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)
Dynamically generate columns in SQL Server
1 Static solution
If you know in advance how many columns (RECEPTION_START/RECEPTION_END pairs) will be present in your data, then you can generate all column/values combinations and build the final table with a simple PIVOT.
These are the test data I used:
This is the code:
--temporary table with sample data
create table #tmp (ACCT_GRP_ID int, RECEPTION_START DATETIME, RECEPTION_END DATETIME)
--populate test data
insert into #tmp values (26, '20170725 00:09:00', '20170725 00:09:15'),(26, '20170725 00:09:15', '20170725 00:09:30'),(26, '20170725 00:09:30', '20170725 00:09:45'),(26, '20170725 00:09:45', '20170725 00:10:00'),(27, '20170725 00:15:00', '20170725 00:15:30'),(27, '20170725 00:15:30', '20170725 00:16:00')
--create the new combinations of columns and values and then pivot the data
select * from (
select tt.ACCT_GRP_ID, tt.col, tt.val from(
select *, 'RECEPTION_START_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_START as val FROM (SELECT * , ROW_NUMBER() OVER ( PARTITION by ACCT_GRP_ID Order by RECEPTION_START ) AS ID FROM #tmp) t
union all
select *, 'RECEPTION_END_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_END as val FROM (SELECT * , ROW_NUMBER() OVER (PARTITION by ACCT_GRP_ID Order by RECEPTION_START) AS ID FROM #tmp) t
) tt
) ttt
PIVOT ( max(val) for Col in
(
[RECEPTION_START_1],
[RECEPTION_END_1],
[RECEPTION_START_2],
[RECEPTION_END_2],
[RECEPTION_START_3],
[RECEPTION_END_3],
[RECEPTION_START_4],
[RECEPTION_END_4]
)
) AS pvt
Outputs these results:
2 Dynamic solution
If you prefer a dynamic solution that handles an arbitrary number of RECEPTION_START/RECEPTION_END columns you can use dynamic sql. Please note that this solution may cause problems such as sql injection, performance issues and all the cons usually associated with dynamic sql.
In this second solution I altered the first two rows of test data setting row 1 with ACCT_GRP_ID = 24 and row 2 with ACCT_GRP_ID = 25
This change shows that the number of columns in the results is different (there will be two RECEPTION_START/RECEPTION_END pairs instead of 4):
declare @max_columns int
declare @counter int
declare @header nvarchar(max)=''
declare @query nvarchar(max)=''
--temporary table with sample data
create table #tmp (ACCT_GRP_ID int, RECEPTION_START DATETIME, RECEPTION_END DATETIME)
--temporary table with new column information
create table #metadata (ACCT_GRP_ID int, col nvarchar(max), [val] datetime, [id] int)
--populate test data
insert into #tmp values (24, '20170725 00:09:00', '20170725 00:09:15'),(25, '20170725 00:09:15', '20170725 00:09:30'),(26, '20170725 00:09:30', '20170725 00:09:45'),(26, '20170725 00:09:45', '20170725 00:10:00'),(27, '20170725 00:15:00', '20170725 00:15:30'),(27, '20170725 00:15:30', '20170725 00:16:00')
--create the new combinations of columns and values
insert into #metadata
select ACCT_GRP_ID, 'RECEPTION_START_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_START as val, t.ID FROM (SELECT * , ROW_NUMBER() OVER ( PARTITION by ACCT_GRP_ID Order by RECEPTION_START ) AS ID FROM #tmp) t
union all
select ACCT_GRP_ID, 'RECEPTION_END_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_END as val, t.ID FROM (SELECT * , ROW_NUMBER() OVER (PARTITION by ACCT_GRP_ID Order by RECEPTION_START) AS ID FROM #tmp) t
--calculate the number of columns of the final table (the maximum number of RECEPTION_START/RECEPTION_END pairs)
select @max_columns= max(m.tot) from(
select COUNT(*)/2 as tot from #metadata group by ACCT_GRP_ID
) m
-- generate the list of columns that will be used for pivoting
set @counter=1
while @counter <= @max_columns
begin
set @header += 'RECEPTION_START_' + cast(@counter as nvarchar(50)) + ', RECEPTION_END_' + cast(@counter as nvarchar(50)) + ', '
set @counter = @counter + 1
end
--remove last unnecessary comma
set @header = SUBSTRING(@header,1,LEN(@header)-1)
--build dynamic query
set @query += ' select * from ('
set @query += ' select ACCT_GRP_ID, col, val from #metadata '
set @query += ' ) tmp'
set @query += ' PIVOT ( max(val) for Col in ('
set @query += @header
set @query += ' )) AS pvt'
--execute dynamic query
exec sp_executesql @query
This is the result set:
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
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
Related Topics
Can a Foreign Key Be Null And/Or Duplicate
Best Way to Check For "Empty or Null Value"
Can Table Columns With a Foreign Key Be Null
Join Two Select Statement Results
SQL Query to Pivot a Column Using Case When
How to Find All Connected Subgraphs of an Undirected Graph
SQL Select Speed Int VS Varchar
How to Determine the Number of Days in a Month in SQL Server
Option (Recompile) Is Always Faster; Why
Error 1046 No Database Selected, How to Resolve
Dynamically Create Columns Sql
How to Get Column Names from a Table in Oracle
Normalize Array Subscripts So They Start With 1
SQL Performance on Left Outer Join VS Not Exists
How to Concatenate Columns in a Postgres Select
Why "Extra Characters After Command" Error Shown for the Sed Command Line Shown