SQL - Pass Columns to Rows

Question

I will want your help. I have one exemple table in SQL(MS SQL Server 2017). I extract one excel file to table. The columns [13],[17], [18] and [19], are columns created by extracting from excel.

I have more dates, here only have 1 exemple with 3 dates.

[13]	[17]	[18]	[19]
	01/04/2020	02/04/2020	03/04/2020
Total	100	200	300
Asset	423	435	533
Revenue	73	73	76

I Want:

Indicator	Date	Value
Total	01/04/2020	100
Total	02/04/2020	200
Total	03/04/2020	300
Asset	01/04/2020	423
Asset	02/04/2020	435
Asset	03/04/2020	533
Revenue	01/04/2020	73
Revenue	02/04/2020	73
Revenue	03/04/2020	76

Answer 1

Hi @ventura ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [13] VARCHAR(20), [17] VARCHAR(20), [18] VARCHAR(20), [19] VARCHAR(20));  
INSERT @tbl ([13], [17], [18], [19]) VALUES  
('', '2020-01-04', '2020-02-04', '2020-03-04'),  
('Total', '100', '200', '300'),  
('Asset',  '423', '435', '533'),  
('Revenue', '73', '73', '76');  
-- DDL and sample data population, end  
  
SELECT * FROM @tbl;  
  
;WITH rs AS  
(  
 SELECT ID, _Date  
 , seq = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  
 FROM @tbl  
 CROSS APPLY (VALUES  
   ([17]),  
   ([18]),  
   ([19])  
    ) v(_Date)  
 WHERE [13] = ''  
), rs2 AS  
(  
 SELECT t.id, t.[13] AS Indicator, v._Value  
 , seq = ROW_NUMBER() OVER (PARTITION BY [13] ORDER BY (SELECT NULL))  
 FROM @tbl AS t  
 CROSS APPLY (VALUES  
   ([17]),  
   ([18]),  
   ([19])  
    ) v(_Value)  
 WHERE [13] <> ''  
)  
SELECT rs2.seq, rs2.Indicator, rs._Date, rs2._Value  
FROM rs   
   INNER JOIN rs2 ON rs2.seq = rs.seq  
ORDER BY rs2.Indicator, rs.seq;  

Output

+-----+-----------+------------+--------+  
| seq | Indicator |   _Date    | _Value |  
+-----+-----------+------------+--------+  
|   1 | Asset     | 2020-01-04 |    423 |  
|   2 | Asset     | 2020-02-04 |    435 |  
|   3 | Asset     | 2020-03-04 |    533 |  
|   1 | Revenue   | 2020-01-04 |     73 |  
|   2 | Revenue   | 2020-02-04 |     73 |  
|   3 | Revenue   | 2020-03-04 |     76 |  
|   1 | Total     | 2020-01-04 |    100 |  
|   2 | Total     | 2020-02-04 |    200 |  
|   3 | Total     | 2020-03-04 |    300 |  
+-----+-----------+------------+--------+  

Answer 2

Hi @ventura
Try this query:

;WITH CTE AS  
(  
 SELECT * FROM [TESTE]  
 UNPIVOT(Column_Vlaue FOR Column_Name IN([17],[18],[19],[110],[111],[112],[113]))U  
),CTE1 AS  
(  
 SELECT * FROM CTE WHERE [13] ='INDICATORS'   
),CTE2 AS  
(  
 SELECT * FROM CTE WHERE [13] IN('Total','ASSET','REVENUE')  
)  
SELECT B.[13] AS INDICATOR,A.Column_Vlaue AS [Date],B.Column_Vlaue   
FROM CTE1 A JOIN CTE2 B ON A.Column_Name=B.Column_Name  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.