converting columns into rows using cursor

Question

I am trying to convert columns into rows using cursors. I know people say cursors are the right thing to do. I know we can use pivot to achieve this. But the problem is that client needs it as cursors with a for loop. Not really sure as to why they need it that way but looks like its easier for them to understand the code.

Table A

ID  FieldA   FieldB  FieldC 
 1   abc      123     xyz

This needs to be converted to

ID  Field0
 1   abc
 1   123
 1   xyz

Any pointers on how to achieve it could be helpful

Thanks

Prady

I am sorry.. it should be unpivot... Yeah i know its pretty baffling some feel cursor to be simple... its just way too complicated for me :( — Prady
– Prady, Commented May 10, 2011 at 12:25
It is UNPIVOT you would need rather than PIVOT and I cannot see how cursor code would be anything but less simple. The basic code you need is SELECT ID,Field0 FROM Table1 UNPIVOT(Field0 FOR FieldName IN (FieldA,FieldB,FieldC)) U — Martin Smith
– Martin Smith, Commented May 10, 2011 at 12:26
What the client wants isn't necessarily what they need...bear that in mind. I can tell you I need a new server machine that runs on 80486 cpus, and you can do it, but it's not the best solution. — JNK
– JNK, Commented May 10, 2011 at 12:46
I suspect it is an interview/teaching task rather then real one. — Dalex
– Dalex, Commented May 10, 2011 at 12:52

Guffa · Accepted Answer · 2011-05-10 12:15:10Z

3

Why not simply:

select ID, FieldA as Field0 from TableA
union all
select ID, FieldB from TableA
union all
select ID, FieldC from TableA

answered May 10, 2011 at 12:15

Guffa

703k112 gold badges760 silver badges1k bronze badges

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

1 Comment

Prady Over a year ago

Thats pretty interesting... Didnt know it could be so simple..But client seems to be adamant on cursors

Collectives™ on Stack Overflow

converting columns into rows using cursor

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related