August 6, 2018 at 4:42 pm
I have a table with name and Amount
Name Amount
rita 100.00
rick 100.00
sam 150.00
harry 200.00
heather 290.00
James 200.00
I was wondering if I can do a dynamic pivot on Amount Column so that my result will be
100 150 200 290
Rita Sam Harry Heather
Ram James
August 6, 2018 at 6:50 pm
Yes. Start at the following article.
http://www.sqlservercentral.com/articles/Crosstab/65048/
If you'd like some more detailed help at the code level, then please see the article at the first link under "Helpful Links" in my signature line for how to post data in a readily consumable manner.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2018 at 9:53 pm
USE tempdb;
GO
SET NOCOUNT ON;
GO
-- DROP TABLE dbo.TestData;
CREATE TABLE dbo.TestData (
[Name] VARCHAR(20) NOT NULL,
Amount MONEY NOT NULL
);
INSERT dbo.TestData (Name, Amount) VALUES
('rita', 100.00),
('rick', 100.00),
('sam', 150.00),
('harry', 200.00),
('heather', 290.00),
('James', 200.00),
('Jason', 200),
('Jeff', 290),
('Shan', 200);
-- SELECT * FROM dbo.TestData td;
--==============================================================
--==============================================================
DECLARE -- Note: NVARCHAR(4000) data types are being used so that sys.sp_executesql can be used in the final execution.
@_col_list NVARCHAR(4000) = N'', -- This variable will have the code that will make up the SELECT list of the final dynamic sql
@_sql NVARCHAR(4000) = N'',
@_debug BIT = 0; -- The @_debug variable allows you to toggle between executing the dynamic SQL and printing it. Set the value to 1 to print.
--------------------------------------------
WITH
cte_distinct_amt AS ( -- We don't want duplicate columns so it is necessary to create a distinct set of values.
SELECT DISTINCT
INTAmount = CONVERT(INT, td.Amount), -- This is just an easy way of eliminating the ".00" from the display names.
td.Amount
FROM
dbo.TestData td
)
SELECT TOP (1000000) -- The TOP clause simply allows the ORDER BY to work properly. It has no other putpose.
@_col_list = CONCAT(@_col_list, N',
[', da.INTAmount, N'] = MAX(CASE WHEN np.Amount = ', da.Amount, N' THEN np.Name END)')
FROM
cte_distinct_amt da
ORDER BY
da.Amount;
--------------------------------------------
-- Using an indexed temp table to improve performance of the final select.
SET @_sql = CONCAT(N'
IF OBJECT_ID(''tempdb..#NamePos'', ''U'') IS NOT NULL
BEGIN DROP TABLE #NamePos; END;
SELECT
td.Name,
td.Amount,
rn = ISNULL(ROW_NUMBER() OVER (PARTITION BY td.Amount ORDER BY td.Name), 0)
INTO #NamePos
FROM
dbo.TestData td;
ALTER TABLE #NamePos ADD PRIMARY KEY CLUSTERED (rn, Amount);
SELECT',
STUFF(@_col_list, 1, 1, ''), N'
FROM
#NamePos np
GROUP BY
np.rn;'
);
--------------------------------------------
IF @_debug = 1
BEGIN
PRINT(@_sql);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @_sql;
END;
GO
Results:100 150 200 290
-------------------- -------------------- -------------------- --------------------
rick sam harry heather
rita NULL James Jeff
NULL NULL Jason NULL
NULL NULL Shan NULL
August 7, 2018 at 5:59 am
Nicely documented, Jason. You should write articles.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2018 at 7:54 am
Jeff Moden - Tuesday, August 7, 2018 5:59 AMNicely documented, Jason. You should write articles.
Thank you for the kind feedback Jeff.
I've kicked the idea writing an article around but I have a bad habit of getting bogged down in the minutia, and end up moving it to the back burner to die an unceremonious death.
Besides, I think you already have the definitive "dynamic cross tabs" article. I don't think I'm doing anything so radically different that it warrants a new article.
That said, The Working Days function that that you and Chris cracked for me last year certainly deserves a good write up...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply