Row to Column

• spungus

  SSC-Addicted

  Points: 443

  More actions

  February 17, 2012 at 5:14 pm

  #74111

  Hi to All,

  I have a problem and i don't know how to explain this but here is what i want to do.

  The original report is come from text file so i just export it to table but the text file report

  is very messy and not organize the way we want it.

  Edit: I think the topic that i put is not right,it should be Column to Row or vice verse.

  Here is the sample data.

  DECLARE @t TABLE (id int,date1 VARCHAR(20),col1 VARCHAR(20),col2 VARCHAR(20))

  INSERT INTO @t

  SELECT 1,'1/04/2010','NONE','NONE'

  UNION SELECT 2,'','ID1','INV1'

  UNION SELECT 3,'','Supplier1','PO1'

  UNION SELECT 4,'','Item1','PRICE1'

  UNION SELECT 5,'','ID2','INV2'

  UNION SELECT 6,'','Supplier2','PO2'

  UNION SELECT 7,'','Item2','PRICE2'

  UNION SELECT 8,'1/05/2010','NONE','NONE'

  UNION SELECT 9,'','ID3','INV3'

  UNION SELECT 10,'','Supplier3','PO3'

  UNION SELECT 11,'','Item3','PRICE3'

  SELECT * FROM @t ORDER BY id

  Basically, what i want to do is to make it like this.

  1/04/2010,ID1,INV1,Supplier1,PO1,Item1,PRICE1

  1/04/2010,ID2,INV2,Supplier2,PO2,Item2,PRICE2

  1/05/2010,ID3,INV3,Supplier3,PO3,Item3,PRICE3

  Thanks for any help or tips to do this.

• sengeet86

  Ten Centuries

  Points: 1252

  More actions

  February 17, 2012 at 10:31 pm

  #1448865

  use pivot and unpivot feature of sqlserver.

  http://msdn.microsoft.com/en-us/library/ms177410.aspx

  java programming