Post reply

Tranpose Table Data into .txt file

  • 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

  • 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 @CSV VarChar(50)

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

    From Test

    Where col1 = @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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply