Transposing two rows of data to one row

• alanshawkat

  Grasshopper

  Points: 14

  More actions

  July 4, 2013 at 4:17 am

  #274928

  I have a large table of 2m records and I need to combine every two Document No_ to one row and placing the other fields on one row against it as below:

  Document No_ Dimension Code Dimension Value Code

  ------------------------------------------------------------------------------------------

  SHP99994 DEPARTMENT MHT

  SHP99994 TRADETYPE M

  I would like the code change the above display to the following:

  Document No_ Dimension Value Code_1 Dimension Value Code_2

  -----------------------------------------------------------------------------------------------

  SHP99994 MHT M

  Can you help please?

  Thank you in advance

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  July 4, 2013 at 4:36 am

  #1629798

  SELECT DocumentNo_,

  MAX(CASE WHEN DimensionCode='DEPARTMENT' THEN DimensionValueCode END) AS DimensionValueCode_1,

  MAX(CASE WHEN DimensionCode='TRADETYPE' THEN DimensionValueCode END) AS DimensionValueCode_2

  FROM myTable

  GROUP BY DocumentNo_

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• alanshawkat

  Grasshopper

  Points: 14

  More actions

  July 5, 2013 at 4:32 am

  #1630006

  Thank you very much for your help.

  I have tested the code and It worked .

  Thank you,

  Alan