How to convert below SQL Expression into derived column in SSIS?

VBA HELP PLEASE! How to convert these rows of data under one column into a one column data per row?

• Okay this is using Microsoft excel and the copy-transpose feature does not work on multiple data per row under one column. It only works for single data per column and row. If I do so, it merely pastes the same set of data under one row and column, much the same as simply copy-pasting it. So who can crack this up for me please? Here is the sample data: all under column A 1 bise bisk bite bits bitt bize blab blae blah blam blat blaw bleb bled 2 blet blew blin blip blob bloc blog blot blow blub blue blur boar boas 3 boat bobs bock bode bods body boff bogs bogy boho boil bola bold bole These should be converted to the following (3 columns 14 rows): A B C bise blet boat bisk blew bobs bite blin bock bits blip bode bitt blob bods bize bloc body blab blog boff blae blot bogs blah blow bogy blam blub boho blat blue boil blaw blur bola bleb boar bold bled boas bole Please help me on this one...got a lot more data with it, what I have shown is just part of the whole thing. If there is such a VBA code for it please tell me and will I still be using Alt+F11 and Alt+F8 with Insert Module to use and run it?

• Answer:

  The following macro will function appropriately on the data you have provided, transposing data in 3 rows in 1 column to 3 columns of 14 rows. If you have additional data that will factor into the functionality, the macro will have to be modified. Copy the following macro to the clipboard: Sub Custom_Transpose() Dim i, j Application.ScreenUpdating = False Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1)), TrailingMinusNumbers:=True Columns("A:N").Select Selection.Cut Destination:=Columns("D:Q") Columns("D:Q").AutoFit Range("A1").Select j = 3 For i = 1 To 14 Cells(i, "A").Value = Cells(1, "A").Offset(0, j).Value Cells(i, "B").Value = Cells(1, "A").Offset(1, j).Value Cells(i, "C").Value = Cells(1, "A").Offset(2, j).Value j = j + 1 Next i Columns("D:Q").ClearContents Columns("D:Q").ColumnWidth = 8.43 End Sub Press ALT + F11 In the menus at the top of the VBE, select INSERT > MODULE Paste the macro into the module editing area to the right. Close the VBE and return to the worksheet. Press ALT + F8 When the Macros window opens, highlight the macro and click 'Options..' Enter a letter to be used as a keyboard shortcut and click 'OK'. Close the Macros window. Select the appropriate worksheet and press Ctrl + your shortcut letter to run the macro. Advise if further modifications are required.