Pivot_Query

CREATE TABLE Merch_appl
(
    id INT ,
    Attri VARCHAR(50) NOT NULL,
    Val VARCHAR(50) NOT NULL
 );
 
INSERT INTO Merch_appl VALUES (1, 'Name', 'Jolly'); 
INSERT INTO Merch_appl VALUES (1, 'Loan', '10000'); 
INSERT INTO Merch_appl VALUES (1, 'Fee', '1000'); 

INSERT INTO Merch_appl VALUES (2, 'Name', 'Molly'); 
INSERT INTO Merch_appl VALUES (2, 'Loan', '5000'); 
INSERT INTO Merch_appl VALUES (2, 'Fee', '500'); 

INSERT INTO Merch_appl VALUES (3, 'Name', 'Polly'); 
INSERT INTO Merch_appl VALUES (3, 'Loan', '75000'); 
INSERT INTO Merch_appl VALUES (3, 'Fee', '10000'); 

SELECT * FROM Merch_appl;
-----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-- Method 1 ( As discussed on call)
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SELECT DISTINCT a.id AS id,
b.val AS NAME,
c.val AS Loan,
d.val AS fee
FROM  Merch_appl a
LEFT JOIN Merch_appl b
ON a.id=b.id AND b.attri='Name'
LEFT JOIN Merch_appl c
ON a.id=c.id AND c.attri='Loan'
LEFT JOIN Merch_appl d
ON a.id=d.id AND d.attri='Fee'

-----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-- Method 2
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SELECT id,
    Max(CASE WHEN Attri='Name' THEN Val END) AS NAME,
    Max(CASE WHEN Attri='Loan' THEN Val END) AS Loan,
    Max(CASE WHEN Attri='Fee' THEN  Val END) AS Fee
FROM Merch_appl 
GROUP BY id;

-----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-- Method 3 (Using PIVOT)
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SELECT *
FROM
(
  SELECT id, VAL, Attri
  FROM Merch_appl
) d
PIVOT
(
  Max(VAL)
  FOR Attri IN ('NAME', 'Loan', 'Fee')
) piv;

-----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-- Method 4 (Using Dynamic pivot - Unable to run and verify in my environement)
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--Create the dynamic query with all the values for
--pivot column at runtime
--LIST ALL FILEDS EXCEPT PIVOT COLUMN

--This parameter will hold the dynamically created SQL script
DECLARE   @SQLQuery AS NVARCHAR(Max);

--This parameter will hold the Pivoted Column values
DECLARE   @PivotColumns AS NVARCHAR(Max)

SELECT   @PivotColumns= Coalesce(@PivotColumns + ',','') + QUOTENAME([Attri])
FROM Merch_appl

/* UNCOMMENT TO SEE THE NEW COLUMN NAMES THAT WILL BE CREATED */
--SELECT   @PivotColumns

--Create the dynamic query with all the values for
--pivot column at runtime
--LIST ALL FILEDS EXCEPT PIVOT COLUMN

SET   @SQLQuery =
   N'SELECT [id],' +   @PivotColumns + '
   INTO Merch_appl_temp
   FROM Merch_appl
   
   PIVOT( MAX([Average Rent])
      FOR [Attri] IN (' + @PivotColumns + ')) AS Q'

/* UNCOMMENT TO SEE THE DYNAMICALLY CREATED SQL STATEMENT */
--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

/* VIEW PIVOTED TABLE RESULTS */
SELECT * FROM Merch_appl_temp