Convert Row values to Columns

• jplittle

  Grasshopper

  Points: 11

  More actions

  March 24, 2010 at 1:21 pm

  #138898

  Hi,

  Hi,

  I have a Microsoft access table that looks like this:

  Field1|Field2|Field3

  1|0|1

  1|10|0.891

  1|20|0.754

  1|30|1

  2|0|1

  2|10|1

  2|20|0.895

  2|30|0.815

  How to "transpose" the Field2 to column into a table?

  Like:

  Field1|0|10|20|30|

  1|1|0.891 | 0.754 | 1

  2|1|1|0.895| 0.815|

• LutzM

  SSC Guru

  Points: 107049

  More actions

  March 24, 2010 at 1:37 pm

  #1139206

  If the values for field2 are fixed (0, 10, 20, 30), then you could either use PIVOT (see BOL for details) or DynamicCrossTab (see the related link in my signature).

  If those values may vary you might want to look into a DynamicCrossTab solution (seet the related link in my sig).

  If you'd like to see a coded and tested solution, please provided table def and sample data as described in the first link in my sig.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• WayneS

  SSC Guru

  Points: 95461

  More actions

  March 24, 2010 at 2:20 pm

  #1139234

  You can do this using the PIVOT operator. See this BOL link for details.

  -- see how this starts off with a table and data in it?

  -- if you had provided us the data in this format,

  -- it would have made things easier for all of the

  -- volunteers on this site to help you out.

  declare @test-2 table (Field1 int, Field2 int, Field3 numeric(8,4))

  insert into @test-2

  SELECT 1, 0, 1 UNION ALL

  SELECT 1, 10, 0.891 UNION ALL

  SELECT 1, 20, 0.754 UNION ALL

  SELECT 1, 30, 1 UNION ALL

  SELECT 2, 0, 1 UNION ALL

  SELECT 2, 10, 1 UNION ALL

  SELECT 2, 20, 0.895 UNION ALL

  SELECT 2, 30, 0.815

  SELECT Field1, [0], [10], [20], [30], [40], [50], [60], [70], [80], [90], [100], [110], [120], [130], [140], [150], [160], [170], [180], [190], [200], [210], [220], [230], [240], [250], [260], [270], [280], [290], [300], [310], [320], [330], [340], [350]

  FROM @test-2

  PIVOT (sum(Field3) FOR Field2 IN ([0], [10], [20], [30], [40], [50], [60], [70], [80], [90], [100], [110], [120], [130], [140], [150], [160], [170], [180], [190], [200], [210], [220], [230], [240], [250], [260], [270], [280], [290], [300], [310], [320], [330], [340], [350])) AS pvt

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• vijay.s

  SSCrazy

  Points: 2161

  More actions

  March 25, 2010 at 1:56 am

  #1139436

  Provide the test script for the same.