Transpose rows into one column

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 2008 Forums

Transact-SQL (2008)

Transpose rows into one column

Author

Topic

MariusC
Starting Member

16 Posts

Posted - 2014-07-07 : 04:56:02

Hi guys,

I have a table with a lot of data like in the example:

unic | nrc | nr_tel | id_stud
-----+-----+--------+---------
2343 | 123 | 354354 | 123
1231 | 432 | 534523 | 324

It has 30k rows like this.
I want to transpose each row into one column. The output should be like:

data
-----+
2343
123
354354
123
1231
432
534523
324

How can I do this ?

Thanks!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-07 : 06:02:01

[code]
;with aCTE
AS
(SELECT 2343 AS unic,123 AS nrc,354354 AS nr_tel,123 AS id_stud UNION ALL
SELECT 1231,432,534523,324)

SELECT *
FROM
(SELECT id_stud,[unic],[nrc],[nr_tel]
FROM aCTE) p
UNPIVOT
(fValue FOR colName IN ([unic],[nrc],[nr_tel])
)AS unpvt;

[/code]

output:
[code]
id_stud fValue colName
123 2343 unic
123 123 nrc
123 354354 nr_tel
324 1231 unic
324 432 nrc
324 534523 nr_tel
[/code]
Can this help you?

sabinWeb MCP

MariusC
Starting Member

16 Posts

Posted - 2014-07-07 : 08:25:35

That was not the output I wanted.

I solved the problem using this query:

select Field from myTable UNPIVOT (Field for ColumnName IN ([unic],[nrc],[nr_tel],[id_stud])) unpvt

Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources