Convert undertimined number of columns into rows
-
Hey,
Not sure if any of you guys can help me with this but I'm pulling my hair out here!!!!
Ok, Basically I've got the following import to do from an excel file (p.s. number of Code*1 & Code*2 combinations are not consistent across rows, I can get a maximum though
)P_Key Name CodeA1 CodeA2 CodeB1 CodeB2 1 My_Company C 3 D 5 2 Your_Company Z 9 T 8 What I need to get is a derived table using the primary key and a set of codes on a single line
i.e.
(P_Key = D_P_Key)
this_pkey D_P_key D_code1 D_code2 1 1 C 3 2 1 D 5 3 2 Z 9 4 2 T 8 If anyone can help and/or has any ideas. Any feedback would be appreciated.
If you need more info let me know.
Cheers,
B.
-
is this a one time deal? You could make this dynamic if not. I think this is what you want:
Insert sqltable (D_pKey,D_code1, D_code2)
select P_Key,
CodeA1, CodeA2
From exceltable
where CodeA1 is not null
and codeA2 is not null
Union all
select P_Key,
CodeB1, CodeB2
From exceltable
where CodeB1 is not null
and codeB2 is not null
...and so on
-
Ideally this would be dynamic because its something I'm going to need alot in the future.
As a background the information is for an online directory taken from a print publication my company publishes.
Being quite a large publisher this scenario comes up often. I've tried to get the information provided to me in a better format but unfortunately that's a no go
Thanks for the reply michanne, I'll give it a try now....
I'll have a go at making it dynamic and post the results,
Any ideas for doing this dynamically?????
-
I had a similar type of problem. I was getting data from a large supplier that had inconsistent data layouts, despite requests otherwise. Sometimes they would even add columns in the middle.
My solution was to have a lookup table that mapped the data to my table. I download the current layout then use substring() to map the data to the appropriate column. The data is pulled into a temp table in a single column. The query is then built dynamically. There is still some manual qa involved in the process but it is a lot cleaner overall.
Hopefully that might give you some ideas 🙂
-
Here is a script that migh also help you come up with a plan. It assumes that the import table is called Excel and the derived table is called derived.
truncate table Derived
declare @sql nvarchar(100)
declare @col1 varchar(30)
declare @col2 varchar(30)
declare @count int
set @count = 1
declare cursCol cursor for select column_name from information_schema.columns where table_name = 'Excel' and column_name like 'CODE%1'
open cursCol
fetch next from cursCol into @col1
while @@fetch_status = 0
begin
set @col2 = replace(@col1,'1','2')
print @col1
print @col2
set @sql = N'insert into derived (THIS_KEY, D_P_KEY,D_CODE_1,D_CODE_2) select ' + convert(varchar,@count) + ', P_KEY, ' + @col1 + ', ' + @col2 + ' from EXCEL'
exec(@SQL)
print @sql
fetch next from cursCol into @col1
end
close cursCol
deallocate cursCol
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply