Multiple Rows into One Row - SQL Server Forums

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)

Multiple Rows into One Row

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2012-05-28 : 03:41:17

I has the following 4 rows,

Company_ID Value
S01 1
S01 2
S01 3

How can I make them into one row and 3 different columns? I don't want to combine them into one column but 3 different columns.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 04:08:22

[code]
select *
from (
select Company_ID, Value,
col_no = row_number() over (partition by Company_ID order by Value)
from tbl
) t
pivot
(
max(Value)
for col_no in ([1], [2], [3])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 14:05:03

what if there are more than three values? in such cases, what 3 values will you choose?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-30 : 02:49:30

You can do a Dynamic Pivot as follows:


--Creating Table

Create Table Ex
(Company_ID varchar(3),
 Value int)
 

--Inserting Sample Values
 
Insert Into Ex
Select 'S01', 1
Union ALL
Select 'S01', 2
Union ALL
Select 'S01', 3


--Dynamic Pivot

Declare @col varchar(max), @sql varchar(max)
Select @col = Coalesce(@col +', ','') + QUOTENAME(Value) From Ex
Set @sql = 'Select Company_Id, '+@col+' From Ex
			Pivot
			(max(Value) For Value In ('+@col+')) As pvt
			Order By Company_Id'
--Print @sql
Exec (@sql)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-30 : 04:01:59

Also refer
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources