DROP TABLE IF EXISTS #Demo;
CREATE TABLE #Demo(Client VARCHAR(50), Country VARCHAR(50),
InvoiceAmount DECIMAL(5,2))
INSERT INTO #Demo
VALUES
('Raja','India',100.00),('Ajit','Begium',200.00),('Sukanya','China',300.00),
('Neha','India',600.00),('Raja','India',300.00),('Ajit','Begium',800.00),
('Sukanya','China',700.00),('Neha','India',600.00),('Raja','Belgium',50.00),
('Neha','France',130.00);
DECLARE @QUERY AS NVARCHAR(MAX);
DECLARE @Col1 AS NVARCHAR(50)='Country'; --'CLIENT'
DECLARE @Col2 AS NVARCHAR(50)='CLIENT'; --'Country';
DECLARE @Col3 AS NVARCHAR(50)='InvoiceAmount';
DECLARE @InputTable AS NVARCHAR(50)='#DEMO';
DECLARE @PivotRows AS NVARCHAR(MAX);
DECLARE @Pvtab TABLE(Cols varchar(max));
DECLARE @Pvtab_task TABLE(Col2 Varchar(max));
DECLARE @Pvtcol Nvarchar(max);
Declare @P_task Nvarchar(max);
SET @P_task = N'SELECT STUFF((SELECT Distinct '',''+''ISNULL(''+'+@col2+'+'',0.00) AS [''+'+@col2+'+'']'' FROM '+@InputTable+' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'
insert into @Pvtab_task (Col2) exec(@P_task);
SET @PivotRows =N'SELECT STUFF((SELECT DISTINCT '',''+QUOTENAME('+@Col2+',''['')
FROM ' + @InputTable + ' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'
INSERT INTO @Pvtab(Cols) EXEC(@PivotRows);
SELECT @Pvtcol=Cols FROM @Pvtab;
---print @pvtcol
SET @QUERY = N'SELECT DISTINCT '+@Col1+','+(select col2 from @Pvtab_task )+' FROM ' +
@InputTable + '
PIVOT (SUM(['+@Col3+']) FOR '+@Col2+' IN ('+@Pvtcol+'))AS PIV'
EXEC(@QUERY)
---------------------------------------
Country Ajit Neha Raja Sukanya
------------------------------------------
Begium 1000.00 0.00 0.00 0.00
Belgium 0.00 0.00 50.00 0.00
China 0.00 0.00 0.00 1000.00
France 0.00 130.00 0.00 0.00
India 0.00 1200.00 400.00 0.00