I have a file that I want to convert the rows to columns. Then, map the current columns to the new column headers. I was trying to pivot, but I can't get it to work, It only like a certain number of columns. My file has 99 rows and 40 columns. Is swing the best way?
I have seen samples wth converting 3 rows. but can't find anything bigger
Is this for data in SQL Server?
inside a ssis package. I do not have a plan on whether to use SQL or not. Not really sure the best and easiest way
Would you post actual data -- DDL and INSERT statement(s) -- rather than just a picture of data. We can't write code against a picture. You seem to have enough experience on forums that you should be aware of the proper way to post question data.
In SQL Server the PIVOT operator does basic row-to-column transposition:
Note that PIVOT requires an aggregate function, and since you're pivoting strings, that limits you to MIN or MAX. It's also not really what you're trying to do, and storing it in a table that's structured the way your example is would be inefficient.
If you're simply transposing rows to columns, or vice versa, and none of the source data originates in a relational database, I would say that SSIS is probably not the right tool. Unless this step is part of a larger data transformation.
Pure column transposition doesn't appear to be available natively in SSIS, but can be done via a Script task:
A cross-tab query should work well here, but without DDL and data it's just too much of a guess to write it from air.
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[WorkingGLDataloadFileEET](
[PK_ID] [int] IDENTITY(1,1) NOT NULL,
[Column 0] nvarchar NULL,
[Column 1] nvarchar NULL,
[Column 2] nvarchar NULL,
[Column 3] nvarchar NULL,
[Column 4] nvarchar NULL,
[Column 5] nvarchar NULL,
[Column 6] nvarchar NULL
) ON [PRIMARY]
INSERT INTO [dbo].[WorkingGLDataloadFileEET]
'Did this financial product have a sustainable investment objective?',
I just added a very sample sample of the file. any help on the query would be great.
column 0 is the one i want as columns going forward
any ideas on this. very stuck on it
You would need to check how to separate this for every row but simple unpivoting gives the following results. Please let me know in case of any questions.
--CREATE TABLE #WorkingGLDataloadFileEET(
--[Column 0] varchar (255) NULL,
--[Column 1] varchar (255) NULL,
--[Column 2] varchar (255) NULL,
--[Column 3] varchar (255) NULL,
--[Column 4] varchar (255) NULL,
--[Column 5] varchar (255) NULL,
--[Column 6] varchar (255) NULL
--INSERT INTO #WorkingGLDataloadFileEET
--'Did this financial product have a sustainable investment objective?',
SELECT * FROM #WorkingGLDataloadFileEET
pvt1.Category AS [Category]
Category FOR ColumnsPivot IN (