Converting row into column using SQL

Question

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

What *was* your attempt? We can't tell you why it didn't work if you don't show us the attempt. — Thom A, Jul 27 '21 at 11:05
Does this answer your question? [SQL Server Converting Rows to Columns](https://stackoverflow.com/questions/38320226/sql-server-converting-rows-to-columns) — Thom A, Jul 27 '21 at 11:06
Does this answer your question? [Convert Rows to Columns - SQL Server](https://stackoverflow.com/questions/29020694/convert-rows-to-columns-sql-server) — Thom A, Jul 27 '21 at 11:07
@Larnu I have 100 rows, using this https://stackoverflow.com/questions/38320226/sql-server-converting-rows-to-columns I may have to give conditions. it didn't helped me. — Newbie, Jul 27 '21 at 11:13
That doesn't show me your attempt, @Newbie ... Also, it doesn't matter if you have 100 rows or 1 million rows; you only want 5 columns, so you only have to define those 5. — Thom A, Jul 27 '21 at 11:15
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — astentx, Jul 27 '21 at 11:31

PSK · Accepted Answer · 2021-07-27T11:54:59.493

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)

Can I again trouble you for calculating the total value of each row and display at the last column pleaseee. — Newbie, Jul 27 '21 at 11:39
are you working on the dynamic pivot or hardcoding the datasource? — PSK, Jul 27 '21 at 11:47
Its not adding the row but its just adding up all values with comma separator like .(20,000.00 ,540,000.00 ,1,000.00). However I am using the fixed set of datasource and not the dynamic one. Can you edit in the fixed set ones. May be it will be much easier for you. — Newbie, Jul 27 '21 at 12:08
SELECT *,ISNULL([AB01],0)+ ISNULL([AB02],0)+ ISNULL([AB03],0) AS 'Total' FROM [TABLE_NAME] PIVOT ( MAX(Amount) FOR Datasource IN ([AB01], [AB02], [AB03]) ) AS PivotTable; — PSK, Jul 27 '21 at 12:15

Converting row into column using SQL

1 Answers1