I’m currently working with a SQL Server database and facing a challenge with transforming my data. The data structure requires me to unpivot numerous columns before moving it to a staging area.
I attempted to use SSIS for this task. While I’m aware that SSIS can handle unpivoting, the process seems cumbersome because I have to manually specify each destination column, and with so many columns involved, it becomes infeasible to write all of them manually.
I initially considered using T-SQL for unpivoting, but my SSIS package doesn’t directly execute T-SQL scripts the way I intended.
I’ve read that there might be a way to leverage C# within SSIS to handle such transformations more efficiently. Is that true? Alternatively, are there any other tools or approaches that you would recommend for efficiently handling this kind of data transformation within the SQL Server environment, possibly still using SSIS?
example:
Original -data
| catgry | item1 | item2 |
|---|---|---|
| One | Two | Three |
Unpivoted-data-
| catgry | items | value |
|---|---|---|
| One | item1 | Tow |
| One | item2 | Three |
i do not want to write Unpivoted column names manually, i have more than 300 items.
Any tips or examples of similar experiences would be greatly appreciated. Thank you!
user27370924 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
Here is an a sample using a bit of JSON (assuming 2016+). Note we only have to filter columns LIKE 'ITEM%'
The JSON portion in the CROSS APPLY will UNPIVOT virtually any table, view or query.
Example or dbFiddle
Select A.ID
,A.FirstName
,A.LastName
,Item = B.[key]
,Value = B.Value
,A.CategoryID
,A.LocationID
From YourTable A
Cross Apply ( Select *
From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper ))
Where [Key] like 'Item%'
) B
-- data preparation - - - - - - - - -- - - - - - - - - - - - - - -
declare @t table
(
catgry varchar(10) not null,
item1 varchar(10) not null,
item2 varchar(10) not null
);
insert into @t values ('One', 'Two', 'Three');
select * from @t;
-- solution - - - - - - - - - - - - - - - - - - - - - - - - - - -
select
t.catgry,
item = i.item,
value = case i.item
when 'item1' then t.item1
when 'item2' then t.item2
end
from
@t t
cross join (values ('item1'), ('item2')) i(item);
2