countrymeister
asked on
How to convert column into row data
I have a table with 6 columns , and each holds a integer value
ID
SIZEXXL
SIZEXL
SIZEL
SIZEM
SIZES
I want to convert these six columns into one column called Size.
SO column size would hold all five values
SIZEXXL
SIZEXL
SIZEL
SIZEM
SIZES
my new table will be
ID
SIZE
ID
SIZEXXL
SIZEXL
SIZEL
SIZEM
SIZES
I want to convert these six columns into one column called Size.
SO column size would hold all five values
SIZEXXL
SIZEXL
SIZEL
SIZEM
SIZES
my new table will be
ID
SIZE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I understand that fyed's query will work
How about this pivot query
SELECT ID, [Size], value
FROM
(
select
ID
,SIZEXXL
,SIZEXL
,SIZEL
,SIZEM
,SIZES
from TableT
) x
unpivot
(
value
for [Size] in
(
,SIZEXXL
,SIZEXL
,SIZEL
,SIZEM
,SIZES
)
) u
How about this pivot query
SELECT ID, [Size], value
FROM
(
select
ID
,SIZEXXL
,SIZEXL
,SIZEL
,SIZEM
,SIZES
from TableT
) x
unpivot
(
value
for [Size] in
(
,SIZEXXL
,SIZEXL
,SIZEL
,SIZEM
,SIZES
)
) u
Post some sample data with expected result
Define 'convert'. Do you wish to import all rows from the old table into this new table with the ID-Size columns? If true, take fyed's query above, and add as the first line INSERT INTO YourNewTable (ID, Size, SomeValue), and that'll do it.