[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Converting Coulmn vales to rows sql server 2008

Posted on 2014-12-04
5
Medium Priority
?
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

Accepted Solution

by:
ste5an earned 1500 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 35

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

650 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