如何在 SQL select 语句中动态创建列 [英] How can I dynamically create columns in SQL select statement
本文介绍了如何在 SQL select 语句中动态创建列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有 3 张桌子.团队，选项，选项团队.
团队持有 TeamId 和 Name
Option 持有 OptionId, OptionGroup
OptionTeam 持有 TeamId、OptionId、OptionGroup
I have 3 tables. Team, Option, OptionTeam.
The Team holds a TeamId and Name
Option holds OptionId, OptionGroup
OptionTeam holds TeamId, OptionId, OptionGroup
select a.TeamId, a.Name
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=4) as Option1,
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=5) as Option2,
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=6) as Option3,
(select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=11) as Option4
from Team a
我想获得一个团队列表，以及额外的列，指示每个团队有多少个选项与每个团队相关联.这是通过上述查询完成的，但我想用表 Option 中的 OptionGroup 值替换 4,5,6,11.
它必须是动态的，因为将来可能会有一个新的OptionGroup，我希望存储过程能够处理它.
I want to get a list of Teams, and extra columns indicating how many options of each group are connected to each Team. This is done by the above query, but I want to replace the 4,5,6,11 with values of OptionGroup from a table Option.
It has to be dynamic, because there might be a new OptionGroup in the future, and I want the stored procedure to be able to handle it.
样本数据:
Team
TeamId
1
2
3
选项
OptionId | OptionGroup
11 | 4
12 | 5
13 | 4
14 | 4
15 | 5
选项团队
TeamId | OptionId | OptionGroup
1 | 11 | 4
1 | 13 | 4
2 | 12 | 5
2 | 14 | 4
3 | 15 | 5
我想得到的列表是
TeamId | Group4 (OptionGroup=4) | Group5 (OptionGroup=5)
1 | 2 | 0
2 | 1 | 1
3 | 0 | 1
推荐答案
您需要一个动态枢轴来执行此操作.这是存储过程:
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
结果:
Teamid 4 5
1 2 0
2 1 1
3 0 1
这篇关于如何在 SQL select 语句中动态创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
