Click here to Skip to main content
15,882,355 members

Comments by Andrius Leonavicius (Top 200 by date)

Andrius Leonavicius 23-May-15 12:33pm View    
My virtual +5! :)
Andrius Leonavicius 22-May-15 20:00pm View    
Hi,

First of all, you are creating #TempTable, which is a local temporary table. This table is only available to the current connection for the user (in your case, a table will be dropped after closing SSMS editor). Temporary tables are used to store data temporarily during a transaction or session. What is the purpose of this temporary table?

Second, you have a trigger, which has nothing to do with the #TempTable. How these two things are related?

Could you tell me what exactly you are trying to achieve?
Andrius Leonavicius 19-May-15 16:56pm View    
Hi,

You need to use ISNULL function for that.

It is quite easy to do this in a static PIVOT query. Instead of selecting all columns as *, you need to write each column with ISNULL. So, change
SELECT *
to
SELECT ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0)

It is harder to do this in a dynamic PIVOT query. I wrote an example for you:

DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));

INSERT INTO @ColumnsTable ([ColumnName])
SELECT DISTINCT '[' + CONVERT(VARCHAR(48), [ITEM_HEAD_ID]) + ']'
FROM [Table];

DECLARE @Columns VARCHAR(MAX), @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);

SET @Columns = (SELECT STUFF((SELECT DISTINCT ', ISNULL(' + CONVERT(VARCHAR(50),
[ColumnName]) + ', 0) AS ' + [ColumnName]
FROM @ColumnsTable
FOR XML PATH('')), 1, 2, ''));

SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR(50), [ColumnName])
FROM @ColumnsTable
FOR XML PATH('')), 1, 2, ''));

SET @TotalColumn = (SELECT STUFF((SELECT DISTINCT ' + ISNULL(' + CONVERT(VARCHAR(50),
[ColumnName]) + ', 0)'
FROM @ColumnsTable
FOR XML PATH('')), 1, 3, ''));

SET @SQL = 'SELECT ' + @Columns + ', (' + @TotalColumn + ') AS [Total]
FROM (SELECT [TRXID],
[ITEM_HEAD_ID],
[ITEM_HEAD_AMT]
FROM [Table]) AS t
PIVOT (MAX([ITEM_HEAD_AMT])
FOR [ITEM_HEAD_ID] IN (' + @PivotColumns + ')) AS p;';

EXEC(@SQL);
Andrius Leonavicius 8-May-15 17:40pm View    
Hi,

Could you show what have you tried so far?
Andrius Leonavicius 17-Oct-14 16:46pm View    
Thank you, Maciej!