CODEWITHSUNDEEP
sqlsql-server-2008
mlane
mlane

Reputation: 11

SQL Server 2008 Unpivot Converting Unknown # of Columns to Rows

Original:

EmpID     Week1     Week2    Week3...(Unknown unmber of weeks)
001       1         2        3
002       2         1        0
003       3         4        0

Target:

EmpID   Attibute    AttributeValue
001         Week1       1
001         Week2       2
001         Week3       3
002         Week1       2
002         Week2       1
002         Week3       0
003         Week1       3
003         Week2       4
003         Week3       0
             .
             .
             .
(will auto generate unknown # of weeks and their values)

I've done my homework, I haven't found a quesiton like this one. The only one I found similar is "Dynamic SQL Server Pivot ( UNPIVOT ) column name to a row value". I tried to run that query but the SQL Server returns no result and messages show "Command(s) completed successfully.)

Upvotes: 1

Views: 4933

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Assuming that your table name is Emp and that you want to unpivot columns that matches Week%:

declare @SQL nvarchar(max)

set @SQL = 
  N'select EmpID, Attribute, AttributeValue from Emp unpivot (AttributeValue for Attribute in ('+
  stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('Emp') and
               C.name like 'Week%'
         for xml path('')), 1, 1, '')+
  N')) as U order by EmpID, Attribute'

exec (@SQL)

Upvotes: 1

Aprillion
Aprillion

Reputation: 22322

well, you can't have "unknown" number of columns in your table, but see Using PIVOT and UNPIVOT:

SELECT EmpID, Attibute, AttributeValue
FROM 
   (SELECT EmpID, Week1, Week2, Week3
   FROM pvt) p
UNPIVOT
   (AttributeValue FOR Attibute IN 
      (Week1, Week2, Week3)
)AS unpvt

Upvotes: 2

Related Questions