A little different table rotation ... 4

[paskuda]

Suppose I have a table like this:
ID NAME
1 John
2 Ann
3 Mike
etc, etc ...
ID column is unique
I need a query to return a table like this:
1 2 3
John Ann Mike

The IDs become the column names and there is only one row of data. Any ideas? I can not hardcode the column names because I don't know how many IDs I have ...

Cheers

 

[ca8msm]

PIVOT


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[paskuda]

Pivot needs some aggregation. I do NOT want to aggregate anything. I just want to rotate the table, so values in column one become column names, and values in column2 create ONE single row. I don't think I can achieve that with pivot ...

 

[ca8msm]

I don't think that matters to be honest (although I could be wrong). The only problem I think you may come across is that for a normal pivot you need to know the values, so unless someone has a better method you may have to use dynamic sql as a workaround.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[ESquared]

I do NOT want to aggregate anything.

What is an aggregation of a single value? What is the sum of a single value? What is the max of a single value? What is the min of a single value?

What you want to do is PIVOT. And as you said, you must aggregate with a pivot. So pivot in light of the questions I just asked.

See this thread for ideas.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[paskuda]

ESquared,
Yeah, I tried doing that, whatever. That was not what I meant, try creating the table I posted and rotate it with PIVOT the way I want - if you can, that's great, give me the code and make me happy I couldn't achieve that with PIVOT.
Anyway, I solved the problem by writing a custom assembly with stored procedure to rotate the table exactly the way I want, it works for me. Still - I'd be more than happy to see a pure TSQL solution.
Thank you for your replies,
Mike

 

[AlexCuse]

paskuda - you seem to be confusing this site with rentacoder.com. This is not a code writing service.

You have been given lots of ideas here that should assist you in writing your query. Quite frankly, this is one of the more common questions asked on the site, and people get a little tired of writing the code for it. I certainly do anyways. So if the examples given aren't cutting it for you, try a search (tek-tips' google search still works well).

If you are using SQL 2000 (you didnt' say) what you will need to do is build an incredibly long query (well, incredibly long if you have over a thousand ID's or so anyways) in a stored procedure that will have a case statement for every single ID value in your table, resulting in something like this:

select max(case ID when 1 then name else '' end) as 1
     , max(case ID when 2 then name else '' end) as 2
     , max(case ID when 3 then name else '' end) as 3
from leTable

But the best advice you could get, is leave the pivoting to excel (or whatever your presentation layer is) where it belongs, if at all possible.

Hope this helps,

Alex




[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom