codeoxygen
asked on
Converting Coulmn vales to rows sql server 2008
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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, MyOrderASKER
Ste5an .. what the [value] ???
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.
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.
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.