Pivoting a table with multiple values

• Cristian Boartes

  SSC Veteran

  Points: 230

  More actions

  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?

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  February 8, 2006 at 5:32 am

  #619485

  There is a nice solution here

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

  The post from Rick Lowry

• Cristian Boartes

  SSC Veteran

  Points: 230

  More actions

  February 8, 2006 at 5:39 am

  #619487

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

  Hmmm

• Liliana Sorrentino

  SSC-Addicted

  Points: 400

  More actions

  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.

   

• Cristian Boartes

  SSC Veteran

  Points: 230

  More actions

  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