0

Could you please kindly help to take a look at my error?

Original Table:

enter image description here

Target Table:

enter image description here

Use below code:

Select DATE,cities,sales
From Test
UNPIVOT
( sales
For cities in (city1, city2, city3)
) AS XYZ

and the result is successful.

However, the city list might grow into city4, city5... So I was trying to use dynamic unpivot to achieve this

Code:

DECLARE @cols AS NVARCHAR(MAX);
select @cols = COALESCE(@cols + ',', '') + quotename(COLUMN_NAME)
From (  Select column_name
        FROM information_schema.columns
        WHERE table_name = 'Test'
        AND COLUMN_NAME <> 'Date' ) AS FiledList
Select DATE,cities,sales
From Test
UNPIVOT
( sales
For cities in (@cols)
) AS XYZ

enter image description here

There seems to be an error, and tried a couple ways, not working.

LeoH
  • 1
  • 1
  • Thanks for reminding, already edited. – LeoH Feb 25 '20 at 07:32
  • You need to form the pivot query dynamically and execute with `sp_executesql` https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15 Do a search there are lots of similar example in SO – Squirrel Feb 25 '20 at 07:33
  • I asked a similar question years ago https://stackoverflow.com/q/30512646/4271117. You need to use dynamic SQL. the @cols you have is a plain string, and therefore the error. [Duplicate of many questions](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) :) – Weihui Guo Feb 28 '20 at 22:34

2 Answers2

0

You might want to consider changing the database so that it is pivoted in the first place. A table with Date, City and Sales would lend itself better to the addition of cities and negate the need for the un-pivot in the first place, assuming you're doing this enable aggregation. Otherwise, you'll need to dynamically create the SQL, which is far from ideal.

Stuie_M
  • 122
  • 11
0
Select DATE,cities,sales
From Test
UNPIVOT
( sales
For cities in (SELECT GROUP_CONCAT(city) FROM cities)
) AS XYZ
Nimantha
  • 6,405
  • 6
  • 28
  • 69
Siva Koteswara Rao
  • 129
  • 1
  • 6