Inefficient pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inefficient pivot

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2012-09-18 : 17:28:29

Hi All,

I currently have to work with a table as per below example, it has many attributes for each customer account, organised into columns for the data type:

DECLARE @test TABLE

(AccountID INT,
DESCRIPTION NVARCHAR(50),
TextValue NVARCHAR(500),
NumericValue NUMERIC,
Currency MONEY,
DateStamp DATETIME)

INSERT INTO @test

SELECT 1, 'Quantity', NULL, 15, NULL, NULL UNION ALL
SELECT 2, 'Telephone Order', 'Payment Declined', NULL, NULL, NULL UNION ALL
SELECT 1, 'Order Total', NULL, NULL, 5689.21, NULL UNION ALL
SELECT 5, 'Dispatch Date', NULL, NULL, NULL, '20120918' UNION ALL
SELECT 5, 'Refund', NULL, NULL, -1500, NULL 

SELECT *
FROM @test

I would like to pivot the data by description (in the columns) to SELECT INTO a new table, but retain the original data types in the new table.

At the moment I just consolidate all data type columns into a helper column by casting all as a varchar, which I can pivot and SELECT INTO the new table. The downside is I lose the original data types, then would have to cast back which isn't very efficient with millions of rows.

Any ideas appreciated.


SELECT * INTO #testtablecheck
FROM (
SELECT AccountID, DESCRIPTION, COALESCE(CAST(TextValue AS NVARCHAR),'') + COALESCE(CAST(NumericValue AS NVARCHAR),'') + COALESCE(CAST(Currency AS NVARCHAR),'') + COALESCE(CAST(DateStamp AS NVARCHAR),'') AS Helper
FROM @test) p
PIVOT (MAX(Helper) FOR p.DESCRIPTION
IN ([Quantity],[Telephone Order],[Order Total],[Dispatch Date],[Refund])) AS pvt

SELECT * FROM #testtablecheck

SELECT COLUMN_NAME, DATA_TYPE FROM tempdb.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME LIKE '%#testtablecheck%'

DROP TABLE #testtablecheck

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 17:30:52

why do you need to make them into a long varchar value? cant you pivot individual columns as is?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Humate
Posting Yak Master

101 Posts

Posted - 2012-09-18 : 17:42:17

Do you mean join a pivot on for each data type?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 12:01:14

nope.

you can do like below


SELECT AccountID,
MAX(CASE WHEN Description='Quantity' THEN TextValue END) AS QtyTV,
MAX(CASE WHEN Description='Quantity' THEN NumericValue END) AS QtyNV,
MAX(CASE WHEN Description='Quantity' THEN Currency END) AS QtyCV,
MAX(CASE WHEN Description='Quantity' THEN DateStamp END) AS QtyDV,
MAX(CASE WHEN Description='Telephone Order' THEN TextValue END) AS TelOrdTV,
MAX(CASE WHEN Description='Telephone Order' THEN NumericValue END) AS TelOrdNV,
MAX(CASE WHEN Description='Telephone Order' THEN Currency END) AS TelOrdCV,
MAX(CASE WHEN Description='Telephone Order' THEN DateStamp END) AS TelOrdDV,
...
FROM Table
GROUP BY AccountID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources