6

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?

pramodc84
  • 1,576
  • 2
  • 25
  • 33
Arian
  • 12,793
  • 66
  • 176
  • 300

5 Answers5

9

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

Bennor McCarthy
  • 11,415
  • 1
  • 49
  • 51
1

Pivot & UnPivot will solve the issue :)

follow the links:

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Naval
  • 344
  • 2
  • 7
1

Below query should do your requirement.

SELECT ID, PAGE, LINE, "C01", C01 FROM TABLE
UNION 
SELECT ID, PAGE, LINE, "C02", C02 FROM TABLE
UNION
SELECT ID, PAGE, LINE, "C03", C03 FROM TABLE
Senthil
  • 106
  • 1
  • 9
  • I guess the idea must be clear in general, but: 1) In Transact-SQL, double quotes are used to delimit *names* (by default, anyway). To delimit string constants, *single* quotes are used. 2) UNION removes duplicate rows. Even if duplicates are unlikely (like in this case), the query plan might still include steps for removing them, which would most probably be a performance hit. Use UNION ALL instead. 3) Aliases should probably be provided for the last two columns. – Andriy M Jul 04 '12 at 06:21
  • Thank you Andriy for clarification. Appreciate it. – Senthil Jul 04 '12 at 06:29
1

You can achieve this by doing something like this in Linq to sql

Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?

Community
  • 1
  • 1
Pravin Pawar
  • 2,559
  • 3
  • 34
  • 40
0

Do a self-join, once for each 1+Nth column. And create a cartesian table.

Self-join is joining the same table to itself. Match it by id, page and line.

Ondra Žižka
  • 43,948
  • 41
  • 217
  • 277