Solved

Converting Coulmn vales to rows sql server 2008

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Count where two id types exist in column 8 27
sql server insert 12 30
T-SQL: "HAVING CASE" Clause 1 23
Sql server, import complete table, using vb.net 9 33
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

786 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