Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: 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 Lakshminarayanan

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do 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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    heh

    "just" predefine the columns...




  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I agree about this being a presntation layer issue....

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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Set based...Thanks to Nigel Rivett

    http://www.sqlteam.com/forums/topic....31&whichpage=2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  9. #9
    Join Date
    Oct 2003
    Posts
    5
    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

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •