i tried to do this !!
This is a different way .. Dynamic SQL ..
if it helps great
please click arrow to the left for "drop create data "
DROP TABLE #data
go
CREATE TABLE #data
(
city VARCHAR(100),
state VARCHAR(100),
population INT,
district1 INT NULL,
district2 INT NULL,
district3 INT NULL
)
go
INSERT INTO #data
SELECT 'Brooklyn',
'NY',
524111,
26,
28,
45
INSERT INTO #data
SELECT 'queens',
'NY',
21022,
32,
NULL,
NULL
INSERT INTO #data
SELECT 'Bronx',
'NY',
321000,
35,
NULL,
NULL
go
SELECT *
FROM #data
go
please click arrow to the left for "dynamic sql"
DECLARE @sql NVARCHAR(max) = ''
DECLARE @counter INT = 1
WHILE @counter <= 3
BEGIN
SET @sql = @sql
+ ' select city,state,population,district'
+ Cast(@counter AS VARCHAR )
+ ' as district from #data union all '
SET @counter = @counter + 1
END
SET @sql = ' select * from ( '
+ LEFT(@sql, Len(@sql)- 9)
+ ') a where district is not null '
PRINT( @sql )
EXEC(@sql)