x

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

Maji gravatar image

Maji
1 1 1 1

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

4 answers: sort voted first
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
more ▼

answered Jul 27, 2010 at 09:19 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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

Maji gravatar image

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x986
x79
x66

asked: Jul 27, 2010 at 04:16 AM

Seen: 2146 times

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