1

I'm using reporting services to make a report graph. However my data looks like this:

Table1
    C01    C02   C03   C04
    1      2     3     4

I need to do a sql query to return data that looks like this:
    Any_Col_name
    1
    2
    3
    4

I'm using MS Reporting Services with a Oracle DB. I cannot restructure the table.

Improve this question
19
  • Any table that has C01..C04 is almost certainly not normalized. Commented Feb 19, 2009 at 23:09
  • 1
    Pax, you're an idiot. He never said he designed the table, just that he's querying it? Go flame somewhere else. Commented Feb 19, 2009 at 23:11
  • And how do you know those are the actual column names? More than likely he's just using C## for an example. What crawled up your ass and died? Commented Feb 19, 2009 at 23:14
  • @Mark, in a word, no! I have as much right here as you. I just tire of seeing questions about tables that, if they'd been done right, wouldn't have to be asked. If you can't see the problem with that table, then you're NOT a database designer as you state. Commented Feb 19, 2009 at 23:15
  • Any query that needs to turn columns into rows is an indication of bad design. Commented Feb 19, 2009 at 23:16

4 Answers 4

Reset to default
6 
select c01 from table
union all
select c02 from table
union all
select c03 from table
union all
select c04 from table
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Why is Mark being downvoted? A union is the correct way to handle this with generic ANSI SQL.
I didn't vote it down, because it works, hence is helpful. Efficiency is another matter. I did flag it offensive because of the language Mark used in his comments - I make no apologies for that.
Free and frank, huh? You're not flagging me as offensive, nor the comment, you're flagging the post, where there's nothing offensive. but I'm not surprised.
That's the way I work. My view is that people should be punished for offensive language and that's the only way to do it for offense in comments. Take it up with the site owners and they can reverse it. If they also tell me not to do it again, I'll abide by that. Or raise a uservoice request.
It's not offensive language, it offended you. Commenting on someone's language skills isn't offensive to the public at large, nor is priggish or snob. Not to mention, there's no "punishment" but to remove the post, a correct answer as you yourself point out. So you think that I will be "punished"
2

If you are using Oracle 11G and above, you can also use unpivot for this, it should be more efficient than the union all (haven't tested this cause I do not have oracle around) 

SELECT Any_Col_name  FROM table   
    UNPIVOT INCLUDE NULLS (Any_Col_name FOR Col IN (C01,C02,C03,C04))
Improve this answer

4 Comments

He didn't say the Oracle was 11g. Unpivot isn't available until then, unless MSRS will just restructure the query sent to Oracle to gather every row and then apply the SQL Server syntax to that result set. Not sure how that will work.
@Mark, I edited the answer to reflect. MSRS will not do anything funky to the queries.
Kewl... Always a good idea to give the latest and greatest as an option... tease people with what they could have if only.
This is the best answer as long as UNPIVOT is supported.
1

Have a look here.

http://support.microsoft.com/kb/175574

It describes how to "rotate" a table in SQL Server. I know you said Oracle buy you might glean something from it.

Improve this answer

2 Comments

That's the wrong direction. it's already flat and he wants it vertical.
I found another answer of yours to mod +1... no hard feelings.
0

We have solved this problem many times. Your best plan of action is to write appropriate PL/SQL functions that will iterate through all of the columns, outputting them as rows. I say to do it this way, because this will probably not be the last time you use this functionality.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.