Tranpose Table Data into .txt file

• simon phoenix-479217

  SSCommitted

  Points: 1573

  More actions

  January 29, 2009 at 7:11 pm

  #99469

  Hi

  Guys

  I have a table like this

  CREATE TABLE [dbo].[Test](

  [col1] [int] NOT NULL,

  [col2] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

  )

  Truncate table dbo.Test

  insert into dbo.Test

  select '2','a' union all

  select '2','b' union all

  select '2','c' union all

  select '3','d' union all

  select '3','e' union all

  select '4','f' union all

  select '5','g' union all

  select '5','h' union all

  select '5','i' union all

  select '5','j' union all

  select * from dbo.Test

  I would like to popluate into .txt file in the following format

  2 a b c

  3 d e

  4 f

  5 g h i j

  Is there a VB script that can be imbeded into Script task tool of SSIS

  Any insight will be highly appreciated

  Thanks

  Simon

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  January 30, 2009 at 2:46 am

  #935349

  [font="Verdana"]I think you better write a UDF like:

  Create Function dbo.Convert_Into_CSV(@ID Int)

  Returns VarChar(50)

  As

  Begin

  Declare@CSVVarChar(50)

  Select@CSV = Case When @CSV Is Null Then col2 Else @CSV + ', ' + col2 End

  FromTest

  Wherecol1 = @Id

  Return@CSV

  End

  Go

  Select dbo.Convert_Into_CSV(2) As CSV_col1

  use this function directly into your select statement.

  Mahesh

  [/font]

  MH-09-AM-8694

• simon phoenix-479217

  SSCommitted

  Points: 1573

  More actions

  January 30, 2009 at 4:23 pm

  #935897

  Hey

  Mahesh

  you are the man, it gave me what i was looking for,perfectly.

  I really appreciate it and thanks for the help

  Thanks

  simon