Post reply

Pivoting a table with multiple values

  • February 8, 2006 at 4:33 am

    #116143

    I have a problem with pivoting a table that has multiple values:

    Exemple

    The table has 2 rows: article, society

    Each article can be found at each society.

    Article cosiety

    A1 1

    A1 2

    A1 3

    A1 4

    A2 1

    A2 2

    A2 3

    I have to pivot this table to look like this:

    Article society

    A1 1,2,3,4

    A2 1,2,3

    And so on...well...im stuck...any ideas?

  • February 8, 2006 at 5:32 am

    #619485

    There is a nice solution here

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=252203#bm252418

    The post from Rick Lowry

  • February 8, 2006 at 5:39 am

    #619487

    Well...i dont have only 3 rows in my table...i have about 70.000...

    Hmmm

  • February 8, 2006 at 5:44 am

    #619490

    Hi,

    Try this:

    DECLARE @article char(10)

    DECLARE @maxarticle char(10)

    SELECT  @article = MIN(article) FROM #test

    SELECT  @maxarticle = MAX(article) FROM #test

    DECLARE @society VARCHAR(8000)

    WHILE  @article <= @maxarticle

           BEGIN

           SET @society = ''

           SELECT @society = @society + rtrim(ltrim(society)) + '; ' FROM #test where article = @article

           SELECT @article, @society

           SELECT @article = MIN(article) FROM #test WHERE article > @article

           END

    Liliana.

     

  • February 8, 2006 at 6:40 am

    #619504

    Well...i think i have done it ...ill keep you posted. I have to integrate it with another procedure ...Lets hope for the best

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

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