Sql Scripts to Transpose columns

Question

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.

Answer 1

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

Answer 2

0

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.

answered Jul 27 '10 at 05:30 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 3

0

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

answered Jul 29 '10 at 11:52 AM

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 '10 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 '10 at 02:19 PM ThomasRushton ♦

(comments are locked)

Sql Scripts to Transpose columns

Follow this question

Related Questions