Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: dispaly data horizontally

    sql 2000:

    Have this in Table:
    A 2ZS
    A RRT
    A ABC
    B 22R
    B 123
    C WWW

    Need this in query result:
    A 2ZS, RRT, ABC
    B 22R, 123
    C WWW

    any ideas?

    thanks
    scott

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, do the pivot using software on the client side. The pivot process ought to be documented in whatever client tool you are using.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    i'm using sql query analyzer

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Query Analyzer is basically a client tool that talks directly to SQL Server. It is not a reporting or presentation tool.

    Try using Microsoft Access, it has a usable report writer. Crystal Reports is good too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2009
    Posts
    62
    sorry,

    i need this done using a stored procedure, so it will have to be sql query analyzer. I was just wondering if there was a function or method to do it.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can easily write a function to do this, but there isn't any function provided "out of the box" to do it because a pivot is really a presentation issue so trying to handle it within SQL Server is a very bad idea.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yup. If your design requires you to perform pivoting (a data presentation issue) on the server side, then there is a big problem with your design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Scott,

    How many records are we talking-about?
    Is this a core part of your system, or is this some sidebar functionality?

    I understand where these guys are coming-from with their issue of this being a presentation issue, but I am also a "practical programmer" where the boss says do it, do it with these tools, and have it on my desk in an hour.

    If that is your situation, there are pivot solutions using SQL--hell, I wrote this exact logic today for a utility I needed.

    The reason I asked the question above is that the solutions are good for reasonably small sets of data, but are not scalable for larger sets of data.

    Let us know.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Sep 2009
    Posts
    62
    Hi Ken,

    this is a 1 time thing and we are talking about 300 - 500 records in SQL 2000.

    thanks
    sCott

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Some variation on this should work for you. As the other guys implied, this is basically presentation code written in SQL.

    There may be a better specification on the cursor type--this is the basic vanilla type and I don't know if there are any guarantees on the order of the data, which is assumed by the logic of the cursor loop.

    You can play with this to learn about cursors, but I typically would only use this code in a utility, or some sidebar production requirement.

    Code:
    declare @TestData
    table	(
    	Category char(1)
    	,Attributes char(3)
    	)
    
    insert into @TestData values('A','2ZS')
    insert into @TestData values('A','RRT')
    insert into @TestData values('A','ABC')
    insert into @TestData values('B','22R')
    insert into @TestData values('B','123')
    insert into @TestData values('C','WWW')
    
    declare @CurrentCategory char(1)
    declare @PreviousCategory char(1)
    declare @CurrentAttribute char(3)
    declare @ResultString varchar(4000)
    declare TransformTestData cursor for
    	(
    	select	Category
    		,Attributes
    	from	@TestData
    	)
    
    open TransformTestData
    fetch next from TransformTestData into @CurrentCategory,@CurrentAttribute
    set @ResultString=@CurrentCategory+' '
    while (@@fetch_status=0)
    	begin
    	if (@PreviousCategory<>@CurrentCategory)
    		begin
    		select left(@ResultString,LEN(@ResultString)-2)
    		set @ResultString=@CurrentCategory+' '
    		end
    	set @ResultString=@ResultString+@CurrentAttribute+', '
    	set @PreviousCategory=@CurrentCategory
    	fetch next from TransformTestData into @CurrentCategory,@CurrentAttribute
    	end
    close TransformTestData
    deallocate TransformTestData
    select left(@ResultString,LEN(@ResultString)-2)
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by scabral7 View Post
    Hi Ken,

    this is a 1 time thing and we are talking about 300 - 500 records in SQL 2000.

    thanks
    sCott
    If it is a "one time thing", then the "practical" thing to do is export it to an spreadsheet and pivot it there. Would take all of 60 seconds, and the resulting file would be much more useful as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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