HOw to covert horizontal resultset to vertical resultset

Hi!
I have a table like this

Continent Country State
Asia India Tn
Asia India AP
Asia India Ka
Asia Pakistan Lh
Asia Pakistan Kh
Asia China Ka
Asia China SS
Asia China OP
Asia China KO
America Canada ON
America Canada TO

Now i want to generate a resulset like this

Continent Country State1 State2 State3 State4
Asia India Tn AP Ka
Asia Pakistan Lh Kh
Asia China Ka SS OP Ko
America Canada ON TO

HOw to do it?
I can do it using the resultset and building the query dynamically by using "Case Statement" but , is there any other way of directly reversing horizontal resultset to vertical ?????.

Kindly reply me

Thank You
Venkat LakshminarayananDo your state have to put in separate columns, or can they be formatted as a delimited string?

Do you have a column which indicates whether a given state is a "state1", "state2", "state3"... state. Otherwise, how do you want to assign states to columns?

Is there a limit on the number of states per country? Do they all have the same number of states?

I'm trying to decide whether a cross-tab query is possible or appropriate.

blindman|||i'd love to see a crosstab query done in sql server

in access, i can do, but in sql server, it is messy code, yes?

i always just tell people to handle it with application code

which includes the "cursor in a stored proc" approach

edit: p.s. toronto is not (yet) a state|||Crosstabs are not that complicated in TSQL. You just have to predefine the columns, which is good practice in Access too even though Access will construct the result set dynamically.

Books Online does a pretty good job of explaining and demonstrating cross-tabl calculation.

In my experience though, cross-tabulation is usually the last step complete in a process prior to formatting the output, and so it is often best handled by the interface (crystal, Access, whatever).

blindman|||heh

"just" predefine the columns...

:rolleyes:|||I agree about this being a presntation layer issue...

but I couldn't resist...just wish I could figure out a set based method...

USE Northwind
GO

CREATE TABLE myTable99 (col1 varchar(20), col2 varchar(20), col3 varchar(20))
GO

INSERT INTO myTable99 (Col1, Col2, Col3)
SELECT 'Asia', 'India', 'Tn' UNION ALL
SELECT 'Asia', 'India', 'AP' UNION ALL
SELECT 'Asia', 'India', 'Ka' UNION ALL
SELECT 'Asia', 'Pakistan', 'Lh' UNION ALL
SELECT 'Asia', 'Pakistan', 'Kh' UNION ALL
SELECT 'Asia', 'China', 'Ka' UNION ALL
SELECT 'Asia', 'China', 'SS' UNION ALL
SELECT 'Asia', 'China', 'OP' UNION ALL
SELECT 'Asia', 'China', 'KO' UNION ALL
SELECT 'America', 'Canada', 'ON' UNION ALL
SELECT 'America', 'Canada', 'TO'
GO

SET NOCOUNT ON

DECLARE IdontWantToUseACursor CURSOR
FOR
SELECT DISTINCT Col1, Col2 FROM myTable99

DECLARE @.Col1 varchar(20), @.Col2 varchar(20), @.Col3 varchar(7000)

DECLARE @.temp TABLE (Col1 varchar(20), Col2 varchar(20), Col3 varchar(7000))

OPEN IdontWantToUseACursor

FETCH NEXT FROM IdontWantToUseACursor
INTO @.Col1, @.Col2

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Col3 = ''
SELECT @.Col3 = @.Col3 + ', ' + Col3 FROM myTable99 WHERE Col1 = @.Col1 AND Col2 = @.Col2
INSERT INTO @.temp (Col1, Col2, Col3) SELECT @.Col1, @.Col2, SUBSTRING(@.Col3,2,LEN(@.Col3)-2)
FETCH NEXT FROM IdontWantToUseACursor
INTO @.Col1, @.Col2
END

CLOSE IdontWantToUseACursor
DEALLOCATE IdontWantToUseACursor

SELECT * FROM @.Temp
GO
SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO|||Set based...Thanks to Nigel Rivett

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29931&whichpage=2|||set based? smoke and mirrors, i say

buried deep within that function is a SELECT statement which is executed for every single distinct combination of col1 and col2

however, there's no getting around the fact that in order to do the denormalization of many rows to one, something like this is necessary, whether in a user-defined function or in application code

but consider the bandwidth, to return multiple rows to the application from the database, where each returned row has "redundant" col1 and col2 values, compared to returning just one with the UDF method

for example, this technique might come in awfully handy when col1 is article_id and col2 is article_text varchar(6000), and the multiple col3 values are keywords describing the article...

rudy|||Hi EveryBody
Thank you all for replying me,I had found a solution and I will mail you that tommorow (if you would like to know),I like to know about Cross tab queries,can anyone help me....?
I have a master table in which Continent, countries and states are defined and What I did is , used a Select Case Statement and a cursor.
I had found a worderful solution for this.But still i like to know about cross tab qeuries which some of you were discussing.

Thank You
Repsor|||Look up CROSSTAB in Books Online. It really does a good jog of explaining it.

And for another example, check out this thread:

http://www.dbforums.com/t956654.html

This is a classic crosstab problem, and hkamatgi's post is a classic crosstab solution.

blindman