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.

more ▼

asked Jul 27, 2010 at 04:16 AM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
     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.

     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)
more ▼

answered Jul 27, 2010 at 09:19 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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!

Jul 27, 2010 at 01:31 PM ThomasRushton ♦♦

@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.

Jul 27, 2010 at 01:39 PM Matt Whitfield ♦♦

@Matt - ah, right. Thanks for the clarification. "reasonably simple" - I like that.

Jul 27, 2010 at 01:51 PM ThomasRushton ♦♦

@ThomasRushton - there you go, edited - reasonably simple :)

Jul 27, 2010 at 01:59 PM Matt Whitfield ♦♦

@Matt: :-)

Jul 27, 2010 at 02:02 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 27, 2010 at 05:30 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

please, can someone show me how to attach sample data for clearer understanding of the source data and the destination table structure.

more ▼

answered Jul 29, 2010 at 11:52 AM

avatar image

1 1 1 1

@Maji - I don't think you can. Also, please use comments rather than adding an answer to your own question...

Jul 29, 2010 at 02:07 PM Matt Whitfield ♦♦

@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.

Jul 29, 2010 at 02:19 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 27, 2010 at 04:16 AM

Seen: 2462 times

Last Updated: Jul 27, 2010 at 05:22 AM

Copyright 2018 Redgate Software. Privacy Policy