Given a temp table with the following data:
| TYPE | NAME | AMT |
|---|---|---|
| A | JOHN | 500 |
| A | MARY | 325 |
| A | BILL | 650 |
| A | FRED | 321 |
| B | JOHN | 405 |
| B | MARY | 218 |
| B | BILL | 945 |
| B | FRED | 735 |
| C | JOHN | 800 |
| C | MARY | 420 |
| C | BILL | 675 |
| C | FRED | 380 |
How can I pivot it to look like this?
| TYPE | JOHN | MARY | BILL | FRED |
|---|---|---|---|---|
| A | 500 | 325 | 650 | 321 |
| B | 405 | 218 | 945 | 735 |
| C | 800 | 420 | 675 | 380 |
/* Create test-data */
DROP TABLE IF EXISTS #LearnPivot;
CREATE TABLE #LearnPivot
(
[TYPE] CHAR(1) NOT NULL,
[NAME] VARCHAR(25) NOT NULL,
[AMT] SMALLINT NOT NULL
);
INSERT INTO #LearnPivot
SELECT 'A', 'JOHN','500' UNION
SELECT 'A', 'MARY','325' UNION
SELECT 'A', 'BILL','650' UNION
SELECT 'A', 'FRED','321' UNION
SELECT 'B', 'JOHN','405' UNION
SELECT 'B', 'MARY','218' UNION
SELECT 'B', 'BILL','945' UNION
SELECT 'B', 'FRED','735' UNION
SELECT 'C', 'JOHN','800' UNION
SELECT 'C', 'MARY','420' UNION
SELECT 'C', 'BILL','675' UNION
SELECT 'C', 'FRED','380';
GO
/* Create Pivot table */
SELECT
PVT.[TYPE],
PVT.[JOHN],
PVT.[MARY],
PVT.[BILL]
FROM
(
SELECT
[TYPE],
[NAME],
[AMT]
FROM
#LearnPivot
) tbl
PIVOT
(
SUM(tbl.AMT) FOR tbl.[NAME] IN ([JOHN],[MARY],[BILL])
) PVT;
I'm going to suggest not using PIVOT for performance and other reasons. I'll be back soon...
Here we go... details are in the comments. For an in-depth explanation, see the article at the first link below. For how to make it "dynamic" base on the names that appear in the data, see the article at the second link below.
--===== Create the test table and populate it on-th-fly
-- This is NOT a part of the solution.
-- We're just creatin a test table here.
DROP TABLE IF EXISTS #SomeTempTable;
GO
SELECT v.*
INTO #SomeTempTable
FROM (VALUES
('A','JOHN',500)
,('A','MARY',325)
,('A','BILL',650)
,('A','FRED',321)
,('B','JOHN',405)
,('B','MARY',218)
,('B','BILL',945)
,('B','FRED',735)
,('C','JOHN',800)
,('C','MARY',420)
,('C','BILL',675)
,('C','FRED',380)
)v(TYPE,NAME,AMT)
GO
--===== This is what is known as a "CROSSTAB".
-- It's similar to a PIVOT but much more "capable"
-- and is generally faster than a PIVOT.
-- This code answers the original question and,
-- maybe, the next question to come from the users
-- about row and column totals.
SELECT Type = IIF(GROUPING(Type) = 0,TYPE,'Total')
,John = SUM(IIF(Name = 'John',Amt,0))
,Mary = SUM(IIF(Name = 'Mary',Amt,0))
,Bill = SUM(IIF(Name = 'Bill',Amt,0))
,Fred = SUM(IIF(Name = 'Fred',Amt,0))
,Total = SUM(Amt)
FROM #SomeTempTable
GROUP BY Type WITH ROLLUP
ORDER BY GROUPING(Type),Type
;
Here are the results from the code above...
And sorry if you got the unedited version of the post above earlier. I did my copy and pastes a bit too early. The post above has been corrected.
Thanks for the replies.
I was out at the grocery store when it occurred to me that I should have given an important bit of additional info.
The full dataset consists of 49 TYPEs, each of which has 90 NAMEs associated with it (the same 90 NAMEs repeated for each TYPE).
Also, I do not need to summarize (totalize) the data in any way - I simply need to turn it sideways.
Lol, I was working on dynamic name code "just in case", and was gonna post it as an example anyway. Just expand the #results table to include however many max names you will need.
Edit: This code initially assumes there's an id / datetime you need if you want to get the names out in the same order they were encountered. If not, if, for example, you want the names in alpha order (which I would, with that many names) just let me know and I will adjust the code accordingly:
Basically, chanage this:
INSERT INTO #results_columns
SELECT ROW_NUMBER() OVER(ORDER BY MIN(id)), NAME
FROM #data
GROUP BY NAME;
to this:
INSERT INTO #results_columns
SELECT ROW_NUMBER() OVER(ORDER BY NAME), NAME
FROM #data
GROUP BY NAME;
DROP TABLE IF EXISTS #data;
CREATE TABLE #data (
ID int IDENTITY(1, 1) NOT NULL,
TYPE varchar(10) NOT NULL,
NAME varchar(50) NOT NULL,
AMT decimal(9, 2) NOT NULL
);
INSERT INTO #data VALUES
('A','JOHN',500),
('A','MARY',325),
('A','BILL',650),
('A','FRED',321),
('A','???',999),
('B','JOHN',405),
('B','MARY',218),
('B','BILL',945),
('B','FRED',735),
('C','JOHN',800),
('C','MARY',420),
('C','BILL',675),
('C','FRED',380);
DROP TABLE IF EXISTS #results;
CREATE TABLE #results (
TYPE varchar(10) NOT NULL PRIMARY KEY,
VALUE1 decimal(9, 2) NULL,
VALUE2 decimal(9, 2) NULL,
VALUE3 decimal(9, 2) NULL,
VALUE4 decimal(9, 2) NULL
);
DROP TABLE IF EXISTS #results_columns;
CREATE TABLE #results_columns (
column_number smallint NOT NULL PRIMARY KEY,
name varchar(10) NOT NULL
);
INSERT INTO #results_columns
SELECT ROW_NUMBER() OVER(ORDER BY MIN(id)), NAME
FROM #data
GROUP BY NAME;
DECLARE @COLUMN_NUMBER smallint;
DECLARE @NAME varchar(10);
DECLARE @SQL nvarchar(max);
DECLARE NAME_CURSOR CURSOR LOCAL FAST_FORWARD FOR
SELECT *
FROM #results_columns
ORDER BY column_number
OPEN NAME_CURSOR;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM NAME_CURSOR INTO @COLUMN_NUMBER, @NAME;
IF @@FETCH_STATUS <> 0
BREAK;
IF NOT EXISTS(SELECT * FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb.dbo.#results') AND name = 'VALUE' + CAST(@COLUMN_NUMBER AS varchar(5)))
BEGIN
PRINT '';
PRINT REPLICATE('!', 40);
RAISERROR('Column "VALUE%i" does not exist in temp table, therefore cannot use name "%s" in final result.', 16, 1, @COLUMN_NUMBER, @NAME);
PRINT REPLICATE('!', 40);
PRINT '';
CONTINUE
END /*IF*/
SET @sql = 'EXEC tempdb.sys.sp_rename ''dbo.#results.VALUE' +
CAST(@COLUMN_NUMBER AS varchar(5)) + ''', ''' + @NAME + ''', ''COLUMN''';
PRINT @sql;
EXEC(@sql);
END /*WHILE*/
INSERT INTO #results
SELECT
d.TYPE,
MAX(CASE WHEN rc.COLUMN_NUMBER = 1 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 2 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 3 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 4 THEN d.AMT END)
FROM #data d
LEFT OUTER JOIN #results_columns rc ON rc.NAME = d.NAME
GROUP BY d.TYPE
SELECT *
FROM #results
ORDER BY TYPE
GO
Ok, rethought this, it's much better to just add columns dynamically as needed for the names needed.
You would also want to dynamically generate a SQL statement to do the INSERT rather than using the one below, which is pre-built for only 5 names. If you want help with that, just let me know.
DROP TABLE IF EXISTS #data;
CREATE TABLE #data (
ID int IDENTITY(1, 1) NOT NULL,
TYPE varchar(10) NOT NULL,
NAME varchar(50) NOT NULL,
AMT decimal(9, 2) NOT NULL
);
INSERT INTO #data VALUES
('A','JOHN',500),
('A','MARY',325),
('A','BILL',650),
('A','FRED',321),
('A','EXTRA_NAME',999),
('B','JOHN',405),
('B','MARY',218),
('B','BILL',945),
('B','FRED',735),
('C','JOHN',800),
('C','MARY',420),
('C','BILL',675),
('C','FRED',380);
DROP TABLE IF EXISTS #results;
CREATE TABLE #results (
TYPE varchar(10) NOT NULL PRIMARY KEY
);
DROP TABLE IF EXISTS #results_columns;
CREATE TABLE #results_columns (
column_number smallint NOT NULL PRIMARY KEY,
name varchar(10) NOT NULL
);
INSERT INTO #results_columns
SELECT ROW_NUMBER() OVER(ORDER BY MIN(id)), NAME
FROM #data
GROUP BY NAME;
DECLARE @COLUMN_NUMBER smallint;
DECLARE @NAME varchar(10);
DECLARE @SQL nvarchar(max);
DECLARE NAME_CURSOR CURSOR LOCAL FAST_FORWARD FOR
SELECT *
FROM #results_columns
ORDER BY column_number
OPEN NAME_CURSOR;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM NAME_CURSOR INTO @COLUMN_NUMBER, @NAME;
IF @@FETCH_STATUS <> 0
BREAK;
SET @sql = 'ALTER TABLE tempdb.dbo.#results ADD [' + @NAME + '] decimal(9, 2) NULL;'
PRINT @sql;
EXEC(@sql);
END /*WHILE*/
INSERT INTO #results
SELECT
d.TYPE,
MAX(CASE WHEN rc.COLUMN_NUMBER = 1 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 2 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 3 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 4 THEN d.AMT END),
MAX(CASE WHEN rc.COLUMN_NUMBER = 5 THEN d.AMT END)
FROM #data d
LEFT OUTER JOIN #results_columns rc ON rc.NAME = d.NAME
GROUP BY d.TYPE
SELECT *
FROM #results
ORDER BY TYPE
GO
Thanks for the help @ScottPletcher
The current dataset has 49 TYPEs and 90 NAMEs. Over time, there may be an occasional addition of one or two TYPEs, but the NAME list could be more dynamic.