Coverting data from column to rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Coverting data from column to rows

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-02-13 : 09:22:15

Hello

I'm trying to convert some data from a column format into rows.

I've done something similar in the past going from rows into columns but this seems a bit trickier to do and I could do with some help.

I currently have the data in the following format:

FIRSTNAME__LASTNAME__VAL1__VAL2__VAL3__VAL4
JOHN_______SMITH_____2_____3_____1_____9
PETER______jONES_____4_____6_____2_____1
SARAH______KELLY_____5_____2_____1_____6

What I need is the data to be in the following format:

FIRSTNAME__LASTNAME__ITEM__VAL
JOHN_______SMITH_____VAL1__2
JOHN_______SMITH_____VAL2__3
JOHN_______SMITH_____VAL3__1
JOHN_______SMITH_____VAL4__9
PETER______jONES_____VAL1__4
PETER______jONES_____VAL2__6
PETER______jONES_____VAL3__2
PETER______jONES_____VAL4__1
SARAH______KELLY_____VAL1__5
SARAH______KELLY_____VAL2__5
SARAH______KELLY_____VAL3__1
SARAH______KELLY_____VAL4__6

Is there a way of getting it in to the above format incorporating the VAL columns in to one columun containing the VAL rows?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:28:26

[code]SELECT FIRSTNAME,LASTNAME,ITEM,VAL
FROM
(SELECT FIRSTNAME,LASTNAME,VAL1,VAL2,VAL3,VAL4
FROM Table)m
UNPIVOT (VAL FOR ITEM IN ([VAL1],[VAL2],[VAL3],[VAL4]))u
[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-13 : 09:28:49

select firstname, lastname, item = 'val1', val = val1 from tbl
union all
select firstname, lastname, item = 'val2', val = val2 from tbl
union all
......

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:30:37

Also


SELECT FIRSTNAME,LASTNAME,ITEM,VAL
FROM
(
SELECT FIRSTNAME,LASTNAME,'VAL1' AS ITEM,VAL1 AS VAL
FROM Table
UNION ALL
SELECT FIRSTNAME,LASTNAME,'VAL2',VAL2
FROM Table
UNION ALL
SELECT FIRSTNAME,LASTNAME,'VAL3',VAL3
FROM Table
UNION ALL
SELECT FIRSTNAME,LASTNAME,'VAL4',VAL4
FROM Table
)t

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-02-13 : 09:41:15

Thanks Visakh thats spot on.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:48:41

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources