Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
/*First I have Created Variable table for Required Data. This will be my Source or Input Table on which I want to apply pivot query*/

DECLARE @Details TABLE (Client NVARCHAR(MAX) NOT NULL,Country NVARCHAR(MAX)NOT NULL,InvoiceAmount INT NOT NULL )
INSERT INTO @Details
SELECT DISTINCT
Cont.CompanyName AS Client,
Coun.Country AS Country,
Sal.InvoiceAmount

FROM Sales Sal
INNER JOIN Contacts Cont ON Sal.ClientID=Cont.ContactID
INNER JOIN Countries Coun ON Cont.CountryID=Coun.CountryID
WHERE Sal.SaleID<>0 AND
Cont.ContactID<>0 AND
Sal.InvoiceStatus IN ('RR','RN','RA','RC','RT');
SELECT * FROM @Details

/*My Output will be
-------------------------------------------------------------------------------
Name Country InvoiceAmount
------------------------------------------------------------------------------
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*/
-------------------------------------------------------------------------------
I am Getting this Output from Above Query
-------------------------------------------------------------------------------
/*I want to apply pivot query such that the output should look like this
A)one Output (when pivot is applied on Country)

Sum(InvoiceAmount)
---------------------------------------------------------------------------------
Name India Belgium China France
---------------------------------------------------------------------------------
Raja 400.00 50.00

Ajit 10000.00

Sukanya 10000.00

Neha 12000.00 130.00
--------------------------------------------------------------------------------

B)Second Output(when pivot is applied on Name)
Sum(InvoiceAmount)
------------------------------------------------------------------------------
Country Raja Ajit Sukanya Neha
------------------------------------------------------------------------------
India 400.00 12000.00

Belgium 50.00 10000.00

china 10000.00

France 130.00
------------------------------------------------------------------------------
All this 'A' and 'B' outputs should come from same query. For that I have used Table Variables and Column Variables.
*/
--------------------------------------------------------------------------------
/*I Wrote the following query but got error. The query is not working properly. Please Help me to solve issues. I am giving my code here*/

/*Purpose of this query is to use Pivot, But data can be fetch from any table and any columns of corresponding table.
Table used here is '@Details' which is Table Variable, As InputTable. Columns can Be anything from '@Details'.
That means If column variable is changed in Query then pivot should be done on respected column
I am doing this because I want to use same query for multiple purpose*/

DECLARE @QUERY AS NVARCHAR(MAX),
@COLSPIVOT AS NVARCHAR(MAX),
@Client AS NVARCHAR(MAX),
@Country AS NVARCHAR(MAX),
@InvoiceAmount AS NVARCHAR(MAX),
@InputTable AS NVARCHAR(MAX),
@COLSUNPIVOT AS NVARCHAR(MAX)
SET @Client = 'Client'
SET @Country = 'Country'
SET @InvoiceAmount = 'InvoiceAmount'
SET @InputTable = '@Details'






SELECT @COLSUNPIVOT = 'STUFF((SELECT '',''+QUOTENAME([@Client])
FROM ' + @InputTable + ' AS C
FOR XML PATH('')), 1, 1, '')'

SELECT @COLSPIVOT = 'STUFF((SELECT DISTINCT '','' + QUOTENAME([@Country])
FROM ' + @InputTable + ' FOR XML PATH(''), TYPE).value(''.'', 'NVARCHAR(MAX)') ,1,1,'')'

SET @QUERY
= 'SELECT DISTINCT '+@Client+', '+@COLSPIVOT+'
FROM
(
SELECT DISTINCT '+@Country+','+@Client+','+@InvoiceAmount+' FROM ' + @InputTable + '
)Te
PIVOT
(
SUM(['+@InvoiceAmount+'])
FOR Country IN ('+@COLSPIVOT+')
) PIV'

EXEC(@QUERY)

What I have tried:

Please help me to solve the issue. I have described my question of SQL PIVOT query. If there is any problem to understand the issue, please let me know.
@Detail Output
-------------------------------------------------------------------------------
Name Country InvoiceAmount
------------------------------------------------------------------------------
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*/
-------------------------------------------------------------------------------
Required Output/*I am trying to type output again because it is wrongly displaying. But no use. So I have Mentioned That it is a sum of InvoiceAmount*/
A)one Output (when pivot is applied on Country)
Sum(InvoiceAmount)
---------------------------------------------------------------------------------
Name India Belgium China France
---------------------------------------------------------------------------------
Raja 400.00 50.00

Ajit 10000.00

Sukanya 10000.00

Neha 12000.00 130.00
--------------------------------------------------------------------------------

B)Second Output(when pivot is applied on Name)
Sum(InvoiceAmount)
------------------------------------------------------------------------------
Country Raja Ajit Sukanya Neha
------------------------------------------------------------------------------
India 400.00 12000.00

Belgium 50.00 10000.00

china 10000.00

France 130.00
------------------------------------------------------------------------------
Posted
Updated 20-Nov-17 21:32pm
v2

1 solution

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
 
Share this answer
 
v2
Comments
HarshadaBS 17-Nov-17 6:03am    
Before trying this query, I want to ask you that, can you explain why did you use temporary table (#Demo), instead of table variable(ex. @Table)?
Santoshkumar Pithani 17-Nov-17 9:30am    
You can use any table this for demo ,but @variable table must be inside dynamic query.
HarshadaBS 18-Nov-17 0:51am    
This Query Works Properly
HarshadaBS 18-Nov-17 1:14am    
To avoid Null values and replace it with '0' then what will be the syntax? I am trying but it gives syntax error.
HarshadaBS 18-Nov-17 1:20am    
/*Something like this...But this query is not right*/
SET @PivotRows =N'SELECT STUFF((SELECT DISTINCT '',ISNULL('+QUOTENAME('+@Col2+',''['')+',0.0)AS'+QUOTENAME('+@Col2+',''['')+''
FROM ' + @InputTable + ' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900