CODEWITHSUNDEEP
sqlsql-serverpivot
Ferby
Ferby

Reputation: 13

SQL Column name is a data and unpivoting not working

fisrt of all thanks for helping! I tried this for 5+ hours and couldn´t fix it..

I Have this table below:

enter image description here

There are 57 date columns.. and my expected result is (just transpose the datas columns so I can run Dax formulas properly in PBI :) )

So, I did this Unpivot:

SELECT [Provincias], [Paises], [Latitude], [Longitude], [DATE], [VALUE]
FROM
(SELECT [Province State], [Country Region], Lat, Long,  30/1/2020,  31/1/2020,  1/2/2020,   2/2/2020,   ...LOT OF DATES....
FROM [data_time_series_19-covid-Confirmed]) PT
UNPIVOT
(Dias FOR ds IN
(   30/1/2020,  31/1/2020,  1/2/2020,   2/2/2020.... LOT OF DATES....)
)AS unpvt

i Tried [date] but didn´t work as well..

It says that: Incorrect Synthax near '30'..

looks like SQL can´t find the columns name if is a date name.. It works perfect for the 3rd line but it doesn´t work in 7th line (awkward o.O)

but when I type:

select [22/1/2020] from [data_time_series_19-covid-Confirmed]

it works properly!

Thanks

Upvotes: 0

Views: 324

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Think you need to put those date references between square brackets

SELECT [Provincias], [Paises], [Latitude], [Longitude], [DATE], [VALUE]
FROM
(SELECT [Province State], [Country Region], Lat, Long,  [30/1/2020],  [31/1/2020],  [1/2/2020],   [2/2/2020]
FROM [data_time_series_19-covid-Confirmed]) PT
UNPIVOT
(Dias FOR ds IN
(   [30/1/2020],  [31/1/2020],  [1/2/2020],   [2/2/2020])
)AS unpvt

Upvotes: 1

Related Questions