Solved

Converting Coulmn vales to rows sql server 2008

Posted on 2014-12-04
5
56 Views
Last Modified: 2014-12-16
I have table result like below



person     ---   palce ---  city --- contry --- value

A               ----  BA     ----  ch--- Ind         --- 3
B              ---     BC      ---  BH--- INd      -- 4

how to convert into table like this:
Some value---- A
Place------- BA
city    ---      Ch
country  --- ind
value     --- 3

Some value---- B
Place------- Bc
city    ---      Bh
country  --- ind
value     --- 4



Can any one help me out
0
Comment
Question by:codeoxygen
5 Comments
 
LVL 1

Expert Comment

by:James Powell
ID: 40480530
Your data headings aren't really clear, because I don't see "Person" repeated in what you're looking for.   I see "Some value".

The quickest way that I would tackle something like that is using the "Transpose" function in Excel, and then re-importing the data.
You might also be able to use a Pivot table in Excel to simply present your data in that format, for the end user.  The latest version of Excel has very powerful Pivot table functions that are user intuitive.
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40480531
Use the UNPIVOT clause. E.g.

SELECT 	*
FROM 	yourTable
UNPIVOT	( [Value] FOR Attribute IN  ( Place, City, Country, [Value] ) ) U;

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480534
With the special mistakes in the original table :-)

Select Person, 'Some Value' as Description, Person as Figure, 1 as MyOrder
From myTable
UNION ALL
Select Person, 'Place', palce, 2
From myTable
UNION ALL
Select Person, 'City', [City], 3
From myTable
UNION ALL
Select Person, 'Country', Contry, 4
From myTable
UNION ALL
Select Person, 'Value', [Value], 5
From myTable
ORDER BY Person, MyOrder

Open in new window

0
 

Author Comment

by:codeoxygen
ID: 40480583
Ste5an .. what the [value] ???
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40480589
You have a column value in your table. Cause value is a reserved word, I've used square brackets. I've to admit, it's untested.

The first [value] in the UNPIVOT clause generates a new column [value] which holds the values, the Attribute column is also generated, holding the name of the columns before the operation. The last [value] is the old value column.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
LTrim & Double Space Correction 5 40
SQL Script to Remove Data from Two Joined Tables 1 20
Query for timesheet application 3 18
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question