SQL Column name is a data and unpivoting not working

Question

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

I Have this table below:

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

What is the dbms (eg MySQL, Oracle), this is important it makes a difference to what solutions are available. Looks like TSQL please choose only relevant tags — Paul Maxwell, Mar 26 '21 at 06:06
https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql it may help you — Bhaskar Reddy, Mar 26 '21 at 06:07
Haven't you answered your own question? You say that when you run "select [22/1/2020] from [data_time_series_19-covid-Confirmed]" it works (and in that query you have square brackets around the column name), but in your unpivot query you don't have the column names in square brackets ..... I'm slightly confused .... — Craig, Mar 26 '21 at 06:08
PaulMaxwell, I am Using SQL SERVER Bhaskar, Thank you, but didn´t work out Craig, no, its not working.. when I put square brackets it doesn´t find the column in the unpivot code. only works when I use" select [data] from".. very confusing — Ferby, Mar 26 '21 at 15:51

score 1 · Answer 1 · answered Mar 26 '21 at 06:08

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

SQL Column name is a data and unpivoting not working

1 Answers1