Solved

Converting Coulmn vales to rows sql server 2008

Posted on 2014-12-04
5
57 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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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