How to arrange output in columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to arrange output in columns?

Author

Topic

andykn
Starting Member

12 Posts

Posted - 2010-01-17 : 13:36:46

I have a table showing which apps are installed on which computers and a report of software installed on computers:

Comp1 App1
Comp1 App2
Comp1 App3
Comp2 App1
Comp2 App3

and so on

What I want is

Comp1 Comp2
App1 App1
App2 App3
App3

And I can't for the life of me think how do do it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-17 : 21:04:11

[code]
declare @apps table
(
comp varchar(5),
app varchar(5)
)
insert into @apps
select 'Comp1', 'App1' union all
select 'Comp1', 'App2' union all
select 'Comp1', 'App3' union all
select 'Comp2', 'App1' union all
select 'Comp2', 'App3'

; with data
as
(
select *, row_no = row_number() over (partition by comp order by comp, app)
from @apps
)
select [Comp1], [Comp2]
from data
pivot
(
max(app)
for comp in ([Comp1], [Comp2])
) p
[/code]

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

andykn
Starting Member

12 Posts

Posted - 2010-01-20 : 14:26:06

Thanks very much KH.

This was just what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources