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)
)
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
I tried this:
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
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
!
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?
Although you only have Name1 and Name2 ATM, this is a typical example of a dynamic pivot.
Create the following 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))
giving
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
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
we obtain
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
Thank you. Your answer has made me realize that using ROW_NUMBER was the wrong approach to doing dynamic PIVOT. I changed the names of the pivot-columns from [1],[2] etc so I now can match them precisely by the aggregation of name and year. Just as 'smirkingman's example so nicely shows!
I like smirkingmans procedure as well. I may come in handy. :)
To clarify this for other users facing the same problem I list here the actual SQL that makes my PIVOT query work (In real life I generate this dynamically):
SELECT
location,
sales_type,
local_id,
house_description,
sales_order,
MAX([name12007]),
MAX([name22007]),
MAX([name12008]),
MAX([name22008]),
MAX([name12009]),
MAX([name22009]),
MAX([name12010]),
MAX([name22010]),
'2010' As [BaseYear]
FROM (
SELECT location, sales_type, local_id, house_description, sales_order, order_year, name,
ISNULL(amount,0) AS [amount],
(name + CONVERT(VARCHAR(MAX),order_year)) AS ColumnIdentifier
FROM @workingData
) AS SourceTable
PIVOT
(
MAX(amount)
FOR ColumnIdentifier IN ( [name12007],[name22007],[name12008],[name22008],
[name12009],[name22009],[name12010],[name22010])
) AS PivotTable
GROUP BY
location, sales_type, local_id, house_description, sales_order
Regards Alex
精彩评论