SQLTeam.com | Weblogs | Forums

Unpivot data

i have the following table name coverage,

city, state, population ,district1,district2,district3 (column heading)
Brooklyn,NY, 524111 ,26, 28, 45
queens, NY, 21022, 32
Bronx, NY, 321000, 35

I WOULD LIKE TO UPIVOT TO SHOW. if any city is associated to multiple district

city, state,population,district --(column heading)
Brooklyn,NY,524111, 26
Brooklyn,NY,524111, 28
Brooklyn,NY,524111, 45
queens,21022 ,32
Bronx,321000 ,35

No usable sample data to test with, but this should do it:

SELECT ytn.city, ytn.state, ytn.population, districts.district
FROM dbo.your_table_name ytn
CROSS APPLY ( VALUES(district1),(district2),(district3) ) AS districts(district)
WHERE districts.district > ''

cross apply does the trick

thanks you

hi

i tried to do this !!
This is a different way .. Dynamic SQL ..
if it helps great :slight_smile:

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)

image