How to convert columns to rows in sql server

Question

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?

Bennor McCarthy · Accepted Answer · 2012-07-04 05:55:43Z

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

edited Jul 4, 2012 at 5:55

answered Jul 4, 2012 at 5:50

Bennor McCarthy

11.7k1 gold badge52 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Senthil · Accepted Answer · 2012-07-04 05:54:22Z

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

answered Jul 4, 2012 at 5:54

Senthil

1061 silver badge9 bronze badges

2 Comments

Andriy M Over a year ago

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.

Senthil Over a year ago

Thank you Andriy for clarification. Appreciate it.

Andriy M · Accepted Answer · 2012-07-04 05:57:41Z

1

Pivot & UnPivot will solve the issue :)

follow the links:

edited Jul 4, 2012 at 5:57

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jul 4, 2012 at 5:48

Naval

3442 silver badges7 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:08:28Z

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?

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Jul 4, 2012 at 6:05

Pravin Pawar

2,5793 gold badges34 silver badges40 bronze badges

Comments

Ondra Žižka · Accepted Answer · 2012-07-04 05:46:08Z

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.

answered Jul 4, 2012 at 5:46

Ondra Žižka

47.2k49 gold badges228 silver badges298 bronze badges

Collectives™ on Stack Overflow

How to convert columns to rows in sql server

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related