Post reply

select query, grouping issue

  • March 29, 2006 at 3:35 am

    #114046

    Hi,

    I would like to know how to solve a little problem I got here. I have some rows that I need to export as columns.

    IE: A table has the following data rows

    Table1

     Person1, Code1

     Person1, Code2

     Person1, Code3

     Person2, Code1

    I need to make a SQL Script that results in the following:

    Person1, Code1, Code2, Code3

    Person2, Code1, null, null

    I will need to do this in tables with more than 1 mill rows. Also the amount of rows for each person can vary, as there can be many Codes for a person.

     

  • March 29, 2006 at 4:55 am

    #629437

    There is a post here that might help (the one from Ryan Randall).

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=263744

    I will admit - I don't know _why_ it works!

    Allen

  • March 29, 2006 at 5:17 am

    #629444

    Did I hear my name?

    It looks to me (though I could be wrong) like that link isn't quite what you're looking for because you don't want to concatenate values, you want them as columns.

    If I were feeling lazy, I'd use this stored procedure (by robvolk):

    http://www.sqlteam.com/item.asp?ItemID=2955

    And the run like this...

    create table ##Table1 (p varchar(10), c varchar(10))

    insert ##Table1

              select 'Person1', 'Code1'

    union all select 'Person1', 'Code2'

    union all select 'Person1', 'Code3'

    union all select 'Person2', 'Code1'

    exec dbo.crosstab 'select p from ##table1 group by p', 'max(c)', 'c', '##table1'

    drop table ##Table1

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 30, 2006 at 2:02 am

    #629742

    Thanks for the fast replies, I'll look at them later today and see if I can use the solutions.

    Best regards

    Musa

  • March 30, 2006 at 2:14 am

    #629747

    Millions of rows you say?...

    Be carefull because you can finally end-up with a record set with hundrends or maybe milions of columns....

    ------------
    When you 've got a hammer, everything starts to look like a nail...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply