asked
Sql Scripts to Transpose columns
i have a data in microsoft excel which always come columns and the columns are many (like 50 columns or more) which i need to update a particular column in the database table. which script can i used to accomplished this task to update this column in my table? E.g code basic_salary, meal_subsidy, Entertainment, Transport, Rent_Subsidy, Callduty 45625 60000 3000 2500 4000 1000 4000 and the destination table looks like this: **Code,** **Item_type**, **Rate** 45625 basic_salary 60000 45625 Meal_subsidy 3000 45625 Entertainment 2500 45625 Transport 4000 45625 Rent_Subsidy 1000 45625 Callduty 4000 This data is much, and it comes very often, how can i achieved this.
I would use Excel to format the data in the right way as it allows for a range of data to be copied and pasted transposed (use Paste Special from right click options). Once the data is fixed then there are loads of ways to import it, OPENROWSET, using linked server, bcp, LogParser and so on. I guess the details behind "much" and "often" will dictate the best option to use.
DECLARE @test TABLE ( Code INT, basic_salary INT, meal_subsidy INT, Entertainment INT, Transport INT, Rent_Subsidy INT, Callduty INT); INSERT INTO @test ([code], [basic_salary], [meal_subsidy], [Entertainment], [Transport], [Rent_Subsidy], [Callduty]) VALUES (45625, 60000, 3000, 2500, 4000, 1000, 4000); And the useful bit SELECT [Code], [Item_type], [Rate] FROM @test UNPIVOT (Rate FOR Item_type IN ([basic_salary], [meal_subsidy], [Entertainment], [Transport], [Rent_Subsidy], [Callduty])) u You haven't really said what SQL Server version you're on - this is 2005+ Just for Thomas - here's the dynamic version. Note that it has to be in a real table for access to the meta-data. CREATE TABLE #test ( Code INT, fred INT, likes INT, chocolate INT, ice_cream INT, for_his INT, breakfast INT); INSERT INTO #test ([code], [fred], [likes], [chocolate], [ice_cream], [for_his], [breakfast]) VALUES (45625, 60000, 3000, 2500, 4000, 1000, 4000); DECLARE @sql [nvarchar] (MAX) SET @sql = 'SELECT [Code], [Item_type], [Rate] FROM #test UNPIVOT (Rate FOR Item_type IN (' + stuff((SELECT ', ' + QUOTENAME([name]) FROM tempdb.sys.columns WHERE [object_id] = OBJECT_ID('tempdb..#test') AND [name] != 'Code' for xml path(''), TYPE).value('.','varchar(max)'), 1, 2, '') + ')) u' EXEC (@sql) DROP TABLE #test
Doesn't this depend upon the Maji's records always having those fields and only those fields? Sorry - I'm feeling a bit hyper-critical today!
@ThomasRushton - Not 'only those fields', no - any additional fields would be ignored. If the fields are variable, then that's a reasonably simple bit of Dynamic SQL, then bob's your mother's brother.
@Matt - ah, right. Thanks for the clarification. "reasonably simple" - I like that.
@ThomasRushton - there you go, edited - reasonably simple :)
please, can someone show me how to attach sample data for clearer understanding of the source data and the destination table structure.
@Maji - I don't think you can. Also, please use comments rather than adding an answer to your own question...
@Maji - tricky. You can post a simplified schema & a few rows of sample data into your question, or, for bigger problems, I guess you're looking at some sort of free hosting. Google Documents, perhaps. And then posting a link to your document in the question.
