Can anyone suggest me how to transpose rows to columns, I have large data set(half million data), read some posts in the forum but found those are used for small data sets. Below is the sample data.
|4||Ohio||234||READY TO WEAR||65||Tie||20151226||1|
|4||Ohio||234||READY TO WEAR||65||Tie||1|
I am not able to understand your data due for formatting. But, typically you would use below query to transpose your rows to columns:
max(case rnum when 0 then price end) as p0,
max(case rnum when 1 then price end ) as p1
max(case rnum when 2 then price end ) as p2
group by 1
mydate rnum price
2016-10-28 0 1
2016-10-28 1 2
2016-10-28 2 3
mydate po p1 p2
2016-10-28 1 2 3
Performencewise i think this should be fine.
This is the normal method of transposing rows to columns, but is not exactly what the OP has in mind. It should look like:
(I changed the spaces to dots and the font to Courier in order to preserve the layout.)
I'm not sure what good this is, but in order to construct a layout like this, you would have to first select all the distinct date values from the table and then write some code to build a dynamic query that uses case operations like the above method, grouping by all the columns.
If this a a large table, there are probably a lot of dates. There is a good chance you could exceed the limit on the number of columns output, if not the limit on the size of the query. Since you have to write code anyway, you might as well just write a post-processor that reformats the output of the original query.