Convert rows to columns

SSIS and Import/Export
1

Hey,
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

Sample file
Sample file1603×720 57 KB

output

output
output1237×720 57.9 KB

2

Is this for data in SQL Server?

3

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

4

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.

5

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:

6

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.

7

SET QUOTED_IDENTIFIER ON
GO

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]
GO

INSERT INTO [dbo].[WorkingGLDataloadFileEET]
([Column 0]
,[Column 1]
,[Column 2]
,[Column 3]
,[Column 4]
,[Column 5]
,[Column 6])
VALUES
('SFDR_SIO_PAST',
'Did this financial product have a sustainable investment objective?',

      'no',
       'no',
       'no',
       'no',
       'no'),

	   
       ('code',
       '0',

      'EGX6',
       'EGKW',
       'EGR9',
       'EGI0',
       'EGSJ');

GO

I just added a very sample sample of the file. any help on the query would be great.

8

column 0 is the one i want as columns going forward

9

any ideas on this. very stuck on it

10

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
--VALUES
--('SFDR_SIO_PAST',
--'Did this financial product have a sustainable investment objective?',

-- 'no',
-- 'no',
-- 'no',
-- 'no',
-- 'no'),

-- ('code',
-- '0',

-- 'EGX6',
-- 'EGKW',
-- 'EGR9',
-- 'EGI0',
-- 'EGSJ');

SELECT * FROM #WorkingGLDataloadFileEET

SELECT
pvt1.Category AS [Category]
FROM #WorkingGLDataloadFileEET

UNPIVOT
(
Category FOR ColumnsPivot IN (
[Column 0],
[Column 1],
[Column 2],
[Column 3],
[Column 4],
[Column 5],
[Column 6])
)pvt1

image
image984×480 12.1 KB