Please consider this table:
ID Page Line C01 C02 C03
---------------------------------------------------------------------
1 122 11 1 0 1
1 123 11 1 1 1
1 124 12 0 0 0
1 125 16 1 0 1
1 127 11 0 1 0
I want to convert this table to this one:
ID Page Line City Value
-----------------------------------------------------------
1 122 11 C01 1
1 122 11 C02 0
1 122 11 C03 1
1 123 11 C01 1
1 123 11 C02 1
1 123 11 C03 1
...
How I can do this in appropriate way?
Use UNPIVOT. Try something like:
SELECT ID, Page, Line, City, Value
FROM SourceTable
UNPIVOT
(Value FOR City IN
(C01, C02, C03)
)AS unpvt;
Where 'SourceTable' is your source table name. (Note: I can't test this at the moment, so it may not be exactly right.)
Full details here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx