SQL Server 2008 PIVOT-如何控制列内容 [英] SQL Server 2008 PIVOT - How to control the column contents
本文介绍了SQL Server 2008 PIVOT-如何控制列内容的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试从具有以下结构的工作表中进行PIVOT查询:
I'm trying to make a PIVOT-query out of a working table with the structure:
DECLARE @workingData TABLE
(
location VARCHAR(20),
name VARCHAR(50),
sales_type VARCHAR(20),
local_id VARCHAR(15),
house_description VARCHAR(40),
sales_order VARCHAR(10),
order_year INT,
amount NUMERIC(14,0)
)
名称列包含两个值之一:'name1'或'name2'.这些必须按如下所述用于分组.
The name column contains one of two values: 'name1' or 'name2'. These must be used in the grouping as described below.
我希望它成为带有列的表:
I'd like it to become a table with the columns:
location
sales_type
local_id
house_description
sales_order
name1_2007
name2_2007
name1_2008
name2_2008
name1_2009
name2_2009
name1_2010
name2_2010
我尝试过:
SELECT
location, sales_type, local_id, house_description, sales_order,
MAX([1]) AS [name1_2007], MAX([2]) AS [name2_2007],
MAX([3]) AS [name1_2008], MAX([4]) AS [name2_2008],
MAX([5]) AS [name1_2009], MAX([6]) AS [name2_2009],
MAX([7]) AS [name1_2010], MAX([8]) AS [name2_2010],
'2010' As [Base Year]
FROM (
SELECT location, sales_type, local_id, house_description, sales_order, order_year, name, amount
,ROW_NUMBER() OVER ( PARTITION BY location, sales_type, local_id, sales_order
ORDER BY order_year, name) AS seq
FROM @workingData
) AS SourceTable
PIVOT
(
MAX(amount)
FOR seq IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
) AS PivotTable
GROUP BY
location, sales_type, local_id, house_description, sales_order
此ALMOST可以工作！ ;)但是我的值没有放在正确的列中.如果特定位置，sales_type，local_id，house_description和sales_order的特定值存在，则始终在[1]列中输出.但这应该确定是name1还是name2及其order_year！
And this ALMOST works! ;) But my values are not placed in the correct columns. If a particular value exists for a certain location, sales_type, local_id, house_description and sales_order it is always outputted in the [1] column. But this should be determined on wether it is name1 or name2 and its order_year!
我知道我看到的是我的row_number操作的直接结果，该操作将seq列计算为1(如果只有一个条目的话).所以也许我在用错误的方式来攻击?
I understand that what I see is a direct result of my row_number operation, that calculates the seq-column to be 1 if only one entry exists. So maybe I'm attacking this the wrong way?
有人可以解决这个问题吗?
Can anybody solve this?
推荐答案
尽管您只有Name1和Name2 ATM，但这是动态数据透视的典型示例.
Although you only have Name1 and Name2 ATM, this is a typical example of a dynamic pivot.
创建以下SP:
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
现在事情变得更容易了.从输入中
Now things become easier. From the input
1 loca namea st1 1 house1 2 2007 1234
2 loca namea st1 1 house1 2 2007 2345
3 loca namea st1 1 house1 2 2007 3456
4 loca namea st1 1 house1 2 2008 6789
5 loca namea st1 1 house1 2 2008 7890
6 loca nameb st1 1 house1 2 2007 1234
7 locc nameb st1 1 house1 2 2007 2345
8 loca nameb st1 1 house1 2 2007 3456
9 loca nameb st1 1 house1 2 2008 6789
10 locc nameb st1 1 house1 2 2008 7890
我们按名称和年份进行汇总
we aggregate by name and year
SELECT
location, sales_type, local_id, house_description, sales_order,
[name] + '_' + cast(order_year AS varchar(20)) as nameyear,
max(amount) as amount
INTO
##crosstab
FROM
working
GROUP BY
location, sales_type, local_id, house_description, sales_order,
[name] + '_' + cast(order_year AS varchar(20))
给予
loca st1 1 house1 2 namea_2007 3456
loca st1 1 house1 2 namea_2008 7890
loca st1 1 house1 2 nameb_2007 3456
loca st1 1 house1 2 nameb_2008 6789
locc st1 1 house1 2 nameb_2007 2345
locc st1 1 house1 2 nameb_2008 7890
然后，使用pivot_sp
then, using the pivot_sp
EXEC pivotsp
'select * from ##crosstab',
'location, sales_type, local_id, house_description, sales_order',
'nameyear',
'max',
'amount',
'##answer'
SELECT
*
FROM
##answer
我们获得
locat st local house so namea_2007 namea_2008 nameb_2007 nameb_2008
loca st1 1 house1 2 3456 7890 3456 6789
locc st1 1 house1 2 NULL NULL 2345 7890
HTH
这篇关于SQL Server 2008 PIVOT-如何控制列内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
