Solved

Converting Coulmn vales to rows sql server 2008

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

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 32

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now