SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #TempData 
(
    Columnname1 NVARCHAR(50),
    Value1 NVARCHAR(50),
    Columnname2 NVARCHAR(50),
    Value2 NVARCHAR(50),
    Columnname3 NVARCHAR(50),
    Value3 NVARCHAR(50)
);

INSERT INTO #TempData (Columnname1, Value1, Columnname2, Value2, Columnname3, Value3)
VALUES
('CustomerID', '1', 'CustomerName', 'A', 'CustomerCity', 'Hyd'),
('CustomerID', '2', 'CustomerName', 'B', 'CustomerCity', 'Mum'),
('CustomerID', '3', 'CustomerName', 'C', 'CustomerCity', 'Pune'),
('CustomerID', '4', 'CustomerName', 'D', 'CustomerCity', 'Nagpur'),
('CustomerID', '5', 'CustomerName', 'E', 'CustomerCity', 'Delhi'),
('CustomerID', '6', 'CustomerName', 'F', 'CustomerCity', 'Noida'),
('CustomerID', '1', 'CustomerDOB', 'July', NULL, NULL),
('CustomerID', '2', 'CustomerDOB', 'August', NULL, NULL),
('CustomerID', '3', 'CustomerDOB', 'September', NULL, NULL),
('CustomerID', '4', 'CustomerDOB', 'October', NULL, NULL),
('CustomerID', '5', 'CustomerDOB', 'November', NULL, NULL),
('CustomerID', '6', 'CustomerDOB', 'December', NULL, NULL);
12 rows affected

-- Query we want to end with:

SELECT CustomerID = Value1, 
  CustomerName = MAX(CASE WHEN ColumnName2 = 'CustomerName' THEN Value2 END),
  CustomerCity = MAX(CASE WHEN ColumnName3 = 'CustomerCity' THEN Value3 END),
  CustomerDOB  = MAX(CASE WHEN ColumnName2 = 'CustomerDOB'  THEN Value2 END)
FROM #TempData
GROUP BY Value1;


CustomerID
CustomerName
CustomerCity
CustomerDOB


1
A
Hyd
July

2
B
Mum
August

3
C
Pune
September

4
D
Nagpur
October

5
E
Delhi
November

6
F
Noida
December


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @cases nvarchar(max),
        @sql   nvarchar(max);

WITH x AS 
(
  SELECT s = N'ColumnName2', c = ColumnName2, v = N'Value2'
    FROM #TempData GROUP BY ColumnName2
  UNION ALL 
  SELECT s = N'ColumnName3', c = ColumnName3, v = N'Value3'
    FROM #TempData GROUP BY ColumnName3
)
SELECT @cases = (SELECT CONCAT(',', char(13), char(10), 
    QUOTENAME(c), N' = MAX(CASE WHEN ', s, 
    N' = ', QUOTENAME(c, char(39)), N' THEN ', v, N' END)') 
    FROM x WHERE c IS NOT NULL
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)');

SELECT @sql = CONCAT(N'SELECT CustomerID = Value1',
  @cases, N'
  FROM #TempData GROUP BY Value1;');

PRINT @sql;
EXEC sys.sp_executesql @sql;


CustomerID
CustomerDOB
CustomerName
CustomerCity


1
July
A
Hyd

2
August
B
Mum

3
September
C
Pune

4
October
D
Nagpur

5
November
E
Delhi

6
December
F
Noida


SELECT CustomerID = Value1,
[CustomerDOB] = MAX(CASE WHEN ColumnName2 = 'CustomerDOB' THEN Value2 END),
[CustomerName] = MAX(CASE WHEN ColumnName2 = 'CustomerName' THEN Value2 END),
[CustomerCity] = MAX(CASE WHEN ColumnName3 = 'CustomerCity' THEN Value3 END)
  FROM #TempData GROUP BY Value1;
Warning: Null value is eliminated by an aggregate or other SET operation.
CustomerID	CustomerName	CustomerCity	CustomerDOB
1	A	Hyd	July
2	B	Mum	August
3	C	Pune	September
4	D	Nagpur	October
5	E	Delhi	November
6	F	Noida	December