Click here to Skip to main content
15,435,848 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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        |


What I have tried:

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         |
Posted
Updated 19-Jul-22 5:23am
v2
Comments
Diogo Tomás 19-Jul-22 11:21am     CRLF
Query to create table CREATE TABLE [dbo].[TESTE]( [13] [nvarchar](255) NULL, [17] [nvarchar](255) NULL, [18] [nvarchar](255) NULL, [19] [nvarchar](255) NULL, [110] [nvarchar](255) NULL, [111] [nvarchar](255) NULL, [112] [nvarchar](255) NULL, [113] [nvarchar](255) NULL ) ON [PRIMARY] GO INSERT [dbo].[TESTE] ([13], [17], [18], [19], [110], [111], [112], [113]) VALUES (N'INDICATORS', N'01/04/2020', N'02/04/2020', N'03/04/2020', N'04/04/2020', N'05/04/2020', N'06/04/2020', N'07/04/2020') INSERT [dbo].[TESTE] ([13], [17], [18], [19], [110], [111], [112], [113]) VALUES (N'Total', N'496', N'508', N'609', N'560', N'483', N'588', N'496') INSERT [dbo].[TESTE] ([13], [17], [18], [19], [110], [111], [112], [113]) VALUES (N'ASSET', N'423', N'435', N'533', N'492', N'415', N'520', N'425') INSERT [dbo].[TESTE] ([13], [17], [18], [19], [110], [111], [112], [113]) VALUES (N'REVENUE', N'73', N'73', N'76', N'68', N'68', N'68', N'71')

1 solution

You need to unpivot data. See: Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]

SQL
WITH CTE AS
(
  SELECT  Indicator, [Val], [ColNo]
  FROM 
  (
    SELECT [13] AS Indicator, [17], [18], [19], [110], [111], [112], [113]
    FROM TESTE
  ) AS PVT
  UNPIVOT
  (
    [Val] FOR [ColNo] IN ([17], [18], [19], [110], [111], [112], [113])  
  ) AS UNPVT
)
SELECT B.Indicator, A.[Val] AS DateVal, B.[Val]
FROM CTE AS A
INNER JOIN CTE AS B
  ON A.[ColNo] = B.[ColNo]
WHERE A.Indicator = 'INDICATORS'
  AND B.Indicator <> 'INDICATORS';


SQL Server 2019 | db<>fiddle[^]
 
Share this answer
 
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


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