I have a query which generates the following result. However I need to again restructure the format. I tried using Convert rows into columns sql server but got an error (cannot create row greater than the allowed..). I am not that good in SQL queries. Please kindly help me on this.
Datasource AcctCode Amount
AB01 100 20,000.00
AB01 101 228.00
AB01 102 5,000.00
AB01 103 10,000.00
AB02 100 540,000.00
AB02 104 305,000.00
AB02 105 21,330.00
AB02 106 10,000.00
AB03 100 1,000.00
AB03 101 54,000.00
AB03 105 30,500.00
AB03 106 40,000.00
AB03 107 5,000.00
Expecting this.
Accout AB01 AB02 AB03 Total
100 20,000.00 540,000.00 1,000.00 561,000.00
101 228.00 - 54,000.00 54,228.00
102 5,000.00 - - 5,000.00
103 10,000.00 - - 10,000.00
104 - 305,000.00 - 305,000.00
105 - 21,330.00 30,500.00 51,830.00
106 - 10,000.00 40,000.00 50,000.00
107 - - 5,000.00 5,000.00
If you want for a fixed set of Datasource, you can transpose rows to columns using PIVOT like the following query.
SELECT *
FROM
[YOUR_TABLE_NAME]
PIVOT
(
MAX(Amount)
FOR Datasource IN ([AB01], [AB02], [AB03])
) AS PivotTable;
If this list of DataSources is dynamic you can use Dynamic PIVOT like the following query.
DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Datasource)
FROM [YOUR_TABLE_NAME]
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
DECLARE @query AS NVARCHAR(max) = ' SELECT *
FROM [YOUR_TABLE_NAME]
PIVOT ( MAX(Amount)
FOR Datasource IN ('+@cols+') ) pvt';
EXECUTE(@query)
EDIT:
Adding Total Coulmn in the last.
DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Datasource)
FROM TABLE_NAME
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
DECLARE @sumcol AS NVARCHAR(max) = ','
+ Stuff((SELECT DISTINCT '+ ISNULL(' + Quotename(Datasource) + ',0)'
FROM TABLE_NAME
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ' as Total';
DECLARE @query AS NVARCHAR(max) = ' SELECT *' + @sumcol + '
FROM TABLE_NAME
PIVOT ( MAX(Amount)
FOR Datasource IN ('+@cols+') ) pvt';
EXECUTE(@query)