SQL Server Performance Forum – Threads Archive

How to do row to column transformation in query

Hi, What would be most optimal way to do row to column transformation using a SQL query? In ORACLE, we could utilize the RANK function (with a PARTITION clause). How can it be achieved in SQL Server 2000? Thanks in advance,
Alvin

Microsoft have added the PIVOT functionality in SQL 2005, similar to the "cross-tab query" that has been available in Office (Excel, Access) for many years. Some people will tell you that this is a presentation issue, to be handled by the client app – like Excel or Access. If you MUST do it in SQL 2000, then you can process each ‘column’ value by adding it to a temp table ("alter table" works fine on temp tables) and insert the aggregate results you need. Then query by SELECT * FROM #tmptable. If you know you have a limited number of values for which you need the aggregate, then you could use dynamic SQL to compile a series of aggregate subqueries as a single main query, and execute that – but you may well run into the 8000 character limitation.

Well, RANK() is available in SQL Server 2005. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] For SQL Server 2000 mostly this is done best at the client, but you can look at BOL for crosstab or pivot queries to see how this can be done with SQL Server 2000. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Start here http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx</a> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Well, RANK() is available in SQL Server 2005. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]<hr height="1" noshade id="quote"></blockquote id="quote"> Is this good or bad? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

If you’re on SQL Server 2000 and need something similar it’s bad. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

That makes sense to me [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] But I was little bit confused by your weeping tag [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

I see…
Actually that was only because we don’t use SQL Server 2005 here right now. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

I think I got a solution to my problem. I used a function returning TABLE. The table defined inside the function has one column defined as identity column. The function looks something like this CREATE FUNCTION funtest ( @code varchar(10) )
RETURNS @option_code TABLE
(
rownum int identity,
option_code varchar(10),
value float
)
AS
BEGIN
INSERT @option_code
SELECT option_code,
num_option_value
FROM test_table
WHERE option_code = @code
RETURN
END This has atleast got me the equivalent of ROWNUM function in ORACLE. The same concept could be modified to obtain the RANK as well by including a GROUP BY clause. This helps me in getting the rank in a table without doing a self join which was the only other way that I could find (for getting rank). Thanks,
Alvin

Try this link:
http://www.sqlteam.com/item.asp?ItemID=5741 Vyas

How is this related to your original question?
What would be most optimal way to do row to column transformation using a SQL query? Madhivanan Failing to plan is Planning to fail

The link and even the suggestions in BOL suggest ways to PIVOT table only when the column on which you pivot have some definite values which can be hardcoded in the CASE statement (like date, quarter etc). But what if the values are not definite (say an identity col). In such cases the ROWNUM or RANK helps. Unfortunatley neither is present in SQL Server 2000. Hence the approach with function TABLE suggested above. Once the ROWNUM or RANK is obtained, the same can be used in the CASE statement to perform the pivot.

FWIW, here’s the link to a stored procedure by SQL Server MVP Itzik Ben-Gan adressing dynamically generated crosstab queries:
http://www.winnetmag.com/Files/09/15608/15608.zip —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

]]>